Replacement for the Flawed UsedRange Property

[Rick Rothstein]

The worksheets object has a UsedRange property that is supposed to return a reference to the rectangular range of cells that contains the cells currently filled with "something" (data, formats, etc.), but it does not always work correctly. The following procedure to demonstrate this should work on all versions of Excel (at least from XL2003, the earliest version I have, upward). Select C5 and use the fill button on the toolbar (for XL2003) or on the Ribbon (for later versions) to color the interior of the cell. Now select F10 and do the same thing. Go into the VB editor and execute this line of code in the Immediate Window...

? ActiveSheet.UsedRange.Address(0,0)

It should return the range C5:F10 because the UsedRange property considers cells with formats as being used. Okay, now go back to the worksheet, select C5 and click the same button you used to fill it to remove the fill (select the "No Fill" option). Now only one cell has anything in it and you should expect the UsedRange to only consist of the single cell F10. Go back into the VB editor and re-execute the line of code posted above... it should still be displaying C5:F10. That is the flaw in the UsedRange property... it does not always update itself in response to changes in the size of the range of cells in use. Unfortunately, there are several other situations where the UsedRange property can end up pointing to the wrong range of cells.

Okay, here is a practical replacement function that will return the actual range of cells with data in them. Note I said "with data in them"... this code does not consider a cell as in use unless it is displaying data or, optionally, has a formula in it displaying the empty string (""). More about that in a moment, but first, here is the code for the function...

Function UsedDataRange(Optional WS As Worksheet, Optional IncludeEmptyFormulas As Boolean) As Range
  Dim LookInConstant As Long, FirstCell As Range, LastCell As Range
  If WS Is Nothing Then Set WS = ActiveWorkbook.ActiveSheet
  If IncludeEmptyFormulas Then
    LookInConstant = xlFormulas
  Else
    LookInConstant = xlValues
  End If
  Set LastCell = WS.Cells(WS.Cells.Find(What:="*", SearchOrder:=xlRows, _
                 SearchDirection:=xlPrevious, LookIn:=LookInConstant).Row, _
                 WS.Cells.Find(What:="*", SearchOrder:=xlByColumns, _
                 SearchDirection:=xlPrevious, LookIn:=LookInConstant).Column)
  Set FirstCell = WS.Cells(WS.Cells.Find(What:="*", After:=LastCell, SearchOrder:=xlRows, _
                  SearchDirection:=xlNext, LookIn:=LookInConstant).Row, _
                  WS.Cells.Find(What:="*", After:=LastCell, SearchOrder:=xlByColumns, _
                  SearchDirection:=xlNext, LookIn:=LookInConstant).Column)
  Set UsedDataRange = WS.Range(FirstCell, LastCell)
End Function

Okay, this function has two optional arguments. The first optional argument allows you to retrieve the used data range for a worksheet other than the active worksheet.... simply pass in a worksheet object for that argument. The second optional argument allows you to control what kind of data should be in the cells to consider it "in use". The argument is a Boolean and for either True or False, a cell displaying data is considered "in use". The difference comes for formulas that are displaying the empty string which most people will normally want considered as a non-filled cell. So, if you pass in False for the second argument (False is the default if you choose to omit the argument), cells with formulas that evaluate to the empty string ("") will not be included in the used data range if they are located along one of the extreme outside borders of the cells displaying data. Remember, this function does not return only the cells meeting the condition, it returns a rectangular range of cells guaranteed to encompass all cells meeting the condition. Obviously, if you pass in True, the function will return the rectangular range encompassing all cells with constants or formulas in them no matter what they are displaying.

[Excel Fox]

Nice one Rick.

Doesn't the idea that UsedRange is flawed come from our notion of a literal meaning of the word UsedRange? From what I gather, a UsedRange is that range of an excel sheet for which Excel has some information. In your example, the cells got colored. And then it gets removed by your action of selecting 'No Fill'. The point to remember is that you have performed an action on it, and Excel is tracking that action. If you do an

ActiveSheet.UsedRange.Address

yes it will return the entire range, contrary to our expectation. Fair enough. But instead of selection 'No Fill', try doing an 'Undo'. And then check the address. You would find that Excel is actually showing a correct reference. So my take is that we tend to think that it is a bug because of our 'idea' of a UsedRange.

[Rick Rothstein]

@Excel Fox,

I understand what you are saying, but I don't think I can agree. It is my view that UsedRange would be totally useless if the range it referenced depended on how the information was modified. There is no way in code to know how a user removed information, so it would be a total guess for the code to decide what the UsedRange property actually refers to. Consider a user who didn't realize the worksheet was scrolled way down (say to Row 10000) and did something to a cell in Column Z that Excel considers putting it in use. The UsedRange would then (probably) be A1:Z10000. Then, later realizing his/her mistake, removed that data. If I want to do something practical with the used range, some kind of looping code might end up iterating A1:Z100 or A1:Z10000... big difference... the code would have no way of knowing if UsedRange is including 9900 rows of blank cells.

I also think Microsoft intended the UsedRange (at least originally) to function like I assumed. Sometimes, but not always, for the same action (or at least I am pretty sure I have seen it for the same action), Excel will correct the UsedRange while other times it won't. Also, the SpecialCells function uses the UsedRange to restrict its actions... for the LastCell property of the SpecialCells function (for my above example), it might point to a cell on Row 100 and other times Row 10000... that seems completely useless for a property named LastCell. How would one put that to practical use?

[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).

[Excel Fox]

If the sheet actually contains information that is represented as colored cells (a traffic light dashboard for example), with no real content within any (or most) cells, the find method would not solve the purpose.

As far as UsedRange is concerned, I for one wouldn't consider its way of working as a flaw. A usedrange should be a usedrange where there is clearly no ambiguity in saying that any cell that was performed an action on is a used cell. And any rectangular area containing used cells will be the used range of that sheet.

So then it comes down to how intuitively the developer wants to look at covering any potential issue (or flaw as Rick puts it) in the way UsedRange works.

[Rick Rothstein]

If the sheet actually contains information that is represented as colored cells (a traffic light dashboard for example), with no real content within any (or most) cells, the find method would not solve the purpose.

That is an excellent point and one I had overlooked.

As far as UsedRange is concerned, I for one wouldn't consider its way of working as a flaw. A usedrange should be a usedrange where there is clearly no ambiguity in saying that any cell that was performed an action on is a used cell. And any rectangular area containing used cells will be the used range of that sheet.

So then it comes down to how intuitively the developer wants to look at covering any potential issue (or flaw as Rick puts it) in the way UsedRange works.

I guess it comes down, as it always should, to the programmer using the right (coding) tool for the right job. Perhaps the real flaw in the UsedRange property is that it does not have an optional argument allowing you to customize the type of usage you want it to calculate... constant data plus displayed data from formulas, constant data plus formulas no matter what they display, any used cell no matter how it is used, or maybe even some others that I have not thought of yet... but I still think it should be fixed to not remember areas that are no longer in use.

[snb]

Another approach ?

To retrieve a 'usedrange', containing numbers:

Sub snb_2_1()
    For Each ar In Cells.SpecialCells(2, 1).Areas
        If IsEmpty(c01) Then c01 = ar.Cells(1).Address
        c02 = Application.Max(c02, ar.Cells(ar.Cells.Count).Row)
        c03 = Application.Max(c03, ar.Cells(ar.Cells.Count).Column)
    Next
    MsgBox "UsedRange = " & Range(c01).Resize(c02 - Range(c01).Row + 1, c03 - Range(c01).Column + 1).Address
End Sub

To retrieve a 'usedrange', containing text:

Sub snb_2_2()
    For Each ar In Cells.SpecialCells(2, 2).Areas
        If IsEmpty(c01) Then c01 = ar.Cells(1).Address
        c02 = Application.Max(c02, ar.Cells(ar.Cells.Count).Row)
        c03 = Application.Max(c03, ar.Cells(ar.Cells.Count).Column)
    Next
    MsgBox "UsedRange = " & Range(c01).Resize(c02 - Range(c01).Row + 1, c03 - Range(c01).Column + 1).Address
End Sub

To retrieve a 'usedrange', containing constants:

Sub snb_2()
    For Each ar In Cells.SpecialCells(2, 2).Areas
        If IsEmpty(c01) Then c01 = ar.Cells(1).Address
        c02 = Application.Max(c02, ar.Cells(ar.Cells.Count).Row)
        c03 = Application.Max(c03, ar.Cells(ar.Cells.Count).Column)
    Next
    MsgBox "UsedRange = " & Range(c01).Resize(c02 - Range(c01).Row + 1, c03 - Range(c01).Column + 1).Address
End Sub

To retrieve a 'usedrange', containing formulae:

Sub snb_4123()
    For Each ar In Cells.SpecialCells(-4123).Areas
        If IsEmpty(c01) Then c01 = ar.Cells(1).Address
        c02 = Application.Max(c02, ar.Cells(ar.Cells.Count).Row)
        c03 = Application.Max(c03, ar.Cells(ar.Cells.Count).Column)
    Next
    MsgBox "UsedRange = " & Range(c01).Resize(c02 - Range(c01).Row + 1, c03 - Range(c01).Column + 1).Address
End Sub

[tfurnivall]

Rick and ExcelFox

It has been instructive to read your debate. However, neither position really covers the need that prompted my request recently. If I delete a set of rows from the worksheet, I would expect to see that deletion - somehow - reflected in UsedRange. Speifically, I am doing the following.

During a process I add occasional new rows to a worksheet - varying number of new rows representing Transactions, Merchants and LookUp entries.

During testing I need to be able to delete these entries so that I can re-run a test. Initially this is a manual process (which seems to work OK). I decided that I needed to have a way of doing it more easily - and at any rate, there are conceivable times when this will be necessary. SO, enter the cmdResetTransactions, cmdResetMerchants and cmdResetLookUp buttons. I used the macro record to 'copy' the code that Excel claims to use and was amazed to discover that there must be something more!

Research indicated that qualification was important (rather than active sheet, or simply a sheetname, I should qualify right up to the Application level). Tried this - still no luck. UsedRange would frequently indicate that some part of the sheet (which I had deleted,using EntireRow) still contained data. SInce my goal is to be able to re-use cells in a predictable manner, this behavior was unacceptable. That's my gripe with UsedRange - when a cell - or a row - has been deleted, it seems that sometimes, under unspecified conditions, it is still, in a sense, there.

Thanks, Rick for your UsedDataRange function - this will probably meet my needs. But I wish that MS would create real, usable documentation for their products, not the half-baked trash that they pump out.

(End rant!)

Thanks for the great work you guys are doing with this forum!

Tony

[Admin]

If you save the workbook after the deletion of the unnecessary row/column, the usedrange property will reset.