Replacement for the Flawed UsedRange Property

**Rick Rothstein** · 04-11-2012, 01:30 AM

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

Code:

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.