Replacement for the Flawed UsedRange Property

[Admin]

Hi Rick,

Again nice one. But I do not seem any practical gain over this function. The major issue is workbook size. Formatting the cells sometimes dramatically increases the workbook size. So I would go with the following procedure which reduces the size of the workbook by deleting all those unwanted formatted cells.

Run the code on each sheet, save and close the workbook, reopen. The usedrange will reset with the actual data.

Option Explicit

Sub CleanWorksheet(Optional ByRef WkSht As Worksheet)
    
    Dim LastRow     As Long
    Dim LastCol     As Long
    
    If WkSht Is Nothing Then Set WkSht = ActiveSheet
    
    With WkSht
        LastRow = .Cells.Find(what:="*", SearchOrder:=xlRows, SearchDirection:=xlPrevious).Row
        LastCol = .Cells.Find(what:="*", SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column
    
        .Range(.Cells(1, LastCol + 1), .Cells(1, .Columns.Count)).EntireColumn.Delete
        .Range(.Cells(LastRow + 1, 1), .Cells(.Rows.Count, 1)).EntireRow.Delete
    End With
    
End Sub

[Rick Rothstein]

@Admin,

Hmm! Maybe I focused too much on the flawed "size" aspect of the UsedRange property and not enough on the "it counts too much stuff when deciding if a cell is 'in use' or not". While the code you posted would reset the right and bottom sides of the UsedRange to just those cells actually containing data, I would think there are many times that action would be too aggressive. It is my understanding that many people pre-format large ranges of cells in anticipation of future data, mainly so that they don't have to continually perform a cell format everytime they enter new data... your suggestied code would be counterproductive to people who do that. This brings me to another benefit of the function I posted... it ignores cell formats when setting the used data range whereas the UsedRange property doesn't. I believe that, in many cases, makes it more practical to use my function as opposed to using the UsedRange property (even, given how it works, if the UsedRange property is tracking the correct cell range size-wise).