Results 1 to 10 of 10

Thread: Replacement for the Flawed UsedRange Property

  1. #1
    Forum Guru Rick Rothstein's Avatar
    Join Date
    Feb 2012
    Posts
    659
    Rep Power
    13

    Replacement for the Flawed UsedRange Property

    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.
    Last edited by Rick Rothstein; 04-11-2012 at 02:11 AM.

  2. #2
    Administrator Excel Fox's Avatar
    Join Date
    Mar 2011
    Posts
    1,401
    Rep Power
    10
    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
    Code:
    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.
    A dream is not something you see when you are asleep, but something you strive for when you are awake.

    It's usually a bad idea to say that something can't be done.

    The difference between dream and aim, is that one requires soundless sleep to see and the other requires sleepless efforts to achieve

    Join us at Facebook

  3. #3
    Forum Guru Rick Rothstein's Avatar
    Join Date
    Feb 2012
    Posts
    659
    Rep Power
    13
    @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?

  4. #4
    Administrator Admin's Avatar
    Join Date
    Mar 2011
    Posts
    1,122
    Rep Power
    10
    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.

    Code:
    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
    Cheers !

    Excel Range to BBCode Table
    Use Social Networking Tools If You Like the Answers !

    Message to Cross Posters

    @ Home - Office 2010/2013/2016 on Win 10 (64 bit); @ Work - Office 2016 on Win 10 (64 bit)

  5. #5
    Forum Guru Rick Rothstein's Avatar
    Join Date
    Feb 2012
    Posts
    659
    Rep Power
    13
    @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).

  6. #6
    Administrator Excel Fox's Avatar
    Join Date
    Mar 2011
    Posts
    1,401
    Rep Power
    10
    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.
    A dream is not something you see when you are asleep, but something you strive for when you are awake.

    It's usually a bad idea to say that something can't be done.

    The difference between dream and aim, is that one requires soundless sleep to see and the other requires sleepless efforts to achieve

    Join us at Facebook

  7. #7
    Forum Guru Rick Rothstein's Avatar
    Join Date
    Feb 2012
    Posts
    659
    Rep Power
    13
    Quote Originally Posted by Excel Fox View Post
    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.

    Quote Originally Posted by Excel Fox View Post
    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.

  8. #8
    Senior Member
    Join Date
    Jun 2012
    Posts
    337
    Rep Power
    12
    Another approach ?

    To retrieve a 'usedrange', containing numbers:
    Code:
    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:
    Code:
    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:
    Code:
    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:
    Code:
    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

  9. #9
    Junior Member
    Join Date
    Dec 2012
    Posts
    12
    Rep Power
    0
    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

  10. #10
    Administrator Admin's Avatar
    Join Date
    Mar 2011
    Posts
    1,122
    Rep Power
    10
    If you save the workbook after the deletion of the unnecessary row/column, the usedrange property will reset.
    Cheers !

    Excel Range to BBCode Table
    Use Social Networking Tools If You Like the Answers !

    Message to Cross Posters

    @ Home - Office 2010/2013/2016 on Win 10 (64 bit); @ Work - Office 2016 on Win 10 (64 bit)

Similar Threads

  1. Clearing UsedRange Of WorkSheet Programmatically
    By tfurnivall in forum Excel Help
    Replies: 1
    Last Post: 12-04-2012, 09:05 AM
  2. Replies: 7
    Last Post: 06-09-2012, 06:45 PM
  3. UsedRange Not Working As Expected
    By littleiitin in forum Excel Help
    Replies: 2
    Last Post: 05-20-2012, 04:16 PM
  4. Using property ColorScaleCriteria color you cells
    By Rasm in forum Download Center
    Replies: 0
    Last Post: 12-04-2011, 09:20 PM
  5. Replacement for TreeView
    By Rasm in forum Excel Help
    Replies: 2
    Last Post: 11-26-2011, 08:20 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •