Results 1 to 10 of 10

Thread: Replacement for the Flawed UsedRange Property

Hybrid View

Previous Post Previous Post   Next Post Next Post
  1. #1
    Administrator Admin's Avatar
    Join Date
    Mar 2011
    Posts
    1,123
    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)

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

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
  •