Reputation: 550

Excel resetting "UsedRange"

Don't know what I'm missing, but the examples I see posted do not appear to work.

I import data from a web query. I set the query to clear unused cells when it re-queries.

I used this imported data to generate a report of variable length.

However if the user (as they need to do in my case) insert rows then the ActiveSheet.UsedRange is expanded. This means I cannot any longer just do a "Ctrl-End" to find the last row in the data set when a new query is performed.

I can easily clear any data with ActiveSheet.UsedRange.Clear. However if the previous query generated a 2 or 3 page report any subsequent query will also be that long even when there is less data because the "UsedRange" still points to that last row way down there.

The examples shown like

ActiveSheet.UsedRange
ActiveSheet.UsedRange.Clear
a = ActiveSheet.UsedRange.Rows.Count

do not reset the range.

MS defines UsedRange as a readOnly property.

It appears what needs to happen is a "File Save" in order to complete the action.

ActiveWorkbook.Save

One post noted that in older versions of Excel you also had to close the workbook and reopen it to complete the action.

I would like to know 1. What is the version cutoff where this behavior changed? 2. Is there some other method using a VBA macro which will reset the range?

Upvotes: 11

Answers (16)

bibidubabidubuu

Reputation: 1

I use the code below before I look for last row:

Thisworkbook.Sheets("Sheet1").UsedRange.Calculate

Then lookup for last row and clear contents between row=2 and last row

Add new data starting from row=2

Finally run the sentence again to get new used range for futher requirement:

Thisworkbook.Sheets("Sheet1").UsedRange.Calculate

Upvotes: 0

Jason Brady

Reputation: 1728

I just wanted to put my 2 cents in here since I was also having this problem until I realized the problem. If you have a UsedRange that is A1:H20 and you delete the row by saying ActiveSheet.Range("A2:H2).Delete you'll find that your UsedRange hasn't updated, it will still show A1:H20. However, if you either say ActiveSheet.Rows(2).Delete or ActiveSheet.Range("A2:H2).EntireRow.Delete You'll find that the UsedRange does update. For some reason you have to delete entire rows or columns (depending your condition) to get that UsedRange to update.

Hope this helps someone!

Upvotes: 0

Salim Talal

Reputation: 1

My Excel VBA program imports data from access database by a given date range. Whenever a new set of data is populated, the worksheet UsedRange does not shrink to reflect only rows with data but instead stays the same even when there are blank rows after the last row with data. This creates difficulty scrolling down the worksheet to view data. So to reset UsedRange in order to just reflect rows with data, see below code. I hope it helps someone.

Sub ResetUsedRange(ByVal oSht As Excel.Worksheet)
    ' ________________________________________________________________
    ' Resetting UsedRange to reset vertical scroll bar on Worksheet,
    ' to reflect ONLY rows with data. This can reset UsedRange on any 
    ' Worksheet. Just pass the worksheet object to this sub.
    ' ================================================================
    Dim iRow1 As Long ' This will get the last row with data
    Dim iRow2 As Long ' This will get the number of rows in UsedRange

    With oSht
        ' Format cells to remove wrap text option
        ' Importing from Access makes some cells with wrap text
        .UsedRange.Cells.WrapText = False

        ' Find last row with data on worksheet
        iRow1 = .Cells(.Rows.count, 1).End(xlUp).Row
    
        ' Get number of rows in UsedRange
        iRow2 = .UsedRange.Rows.count
    
        ' If UsedRange rows exceeds the last row with data
        ' then delete the rows which obviously are blank
        If iRow2 > iRow1 Then
            .Range(.Cells(iRow1 + 1, 1), .Cells(iRow2, 1)).EntireRow.Delete
        End If
    
        ' following code forces UsedRange to recalculate and resets
        ' to reflect ONLY rows with data if blank rows in the UsedRange 
        ' were deleted making the vertical scroll bar to reset itself.
        .UsedRange.Calculate
    End With
End Sub

Upvotes: 0

mcgrailm

Reputation: 17640

This is what ended up working for me. I feel there has to be a better way but no others worked for me.

Sub ClearRangeData()
Dim S1 As Worksheet

Set S1 = Sheets("Your Sheet Name")     'Define what sheets we are using

'------- Remove all the old data -----
S1.Activate
With ActiveSheet
    S1_rows = S1.UsedRange.Rows.Count
    For I = S1_rows To 1 Step -1
        Cells(I, 1).EntireRow.Delete
    Next
End With
End Sub

Upvotes: 1

JosephD

Reputation: 119

This worked for me:

Worksheets("Sheet1").UsedRange.Clear
Worksheets("Sheet1").UsedRange = ""

It appears that inserting a value into the UsedRange resets it. After this action I can go

MyCurrentRow = Worksheets("Sheet1").Range("A:A").SpecialCells(xlCellTypeLastCell).Row

MyCurrentRow comes now back as 1, and I can just count from there. When I did not assign a value into UsedRange, that LastCell value did not reset. No Save required.

Upvotes: -1

Harley

Reputation: 81

I only needed to use Worksheets("Sheet1").UsedRange.Calculate after deleting rows to reset the range.

Upvotes: 8

AzzaClazza

Reputation: 111

This is the solution I used.

Sub CorrectUsedRange()
    Dim values
    Dim usedRangeAddress As String
    Dim r As Range
    'Get UsedRange Address prior to deleting Range
    usedRangeAddress = ActiveSheet.UsedRange.Address
    'Store values of cells to array.
    values = ActiveSheet.UsedRange
    'Delete all cells in the sheet
    ActiveSheet.Cells.Delete
    'Restore values to their initial locations
    Range(usedRangeAddress) = values
End Sub

Upvotes: 0

sirplus

Reputation: 535

select cell 1,1 in any sheets you want to reset the UsedRange property
Calculate all worksheets in all open workbooks, regardless of whether they changed since last calculation (To Calculate Fully Ctrl+Alt+F9)
Save the workbook

Works for me on all versions of excel

Upvotes: 2

Joe Cross

Reputation: 53

Thanks to Claus for having the correct answer, but it is incomplete, and he completed it with a comment on the main post. I'll answer here to combine the useful bits into a single working solution.

Note: I have not tried variations to see which steps are necessary, but these steps work to clear unnecessary used range.

Sub ResetUsedRange()
dim rngToDelete as range

set rngToDelete = 'Whatever you want

rngToDelete.EntireRow.Clear
rngToDelete.EntireRow.Select
Selection.Delete Shift:=xlUp

ActiveWorkbook.Save 'This step has been stated to be necessary for the range to reset.

End Sub

Upvotes: 0

CyberNot

Reputation: 1

This works for me in Excel 2010:

Worksheets("Sheet1").UsedRange.Clear  
Worksheets("Sheet1").UsedRange.Calculate

Upvotes: -2

Monosabio

Reputation: 41

Best code that worked for me:

Sub DeleteUnused()
Dim myLastRow As Long
Dim myLastCol As Long
Dim dummyRng As Range
Dim AnyMerged As Variant
'http://www.contextures.on.ca/xlfaqApp.html#Unused
'Helps to reset the usedrange by deleting rows and columns AFTER your true used range

    'Check for merged cells
    AnyMerged = ActiveSheet.UsedRange.MergeCells
    If AnyMerged = True Or IsNull(AnyMerged) Then
        MsgBox "There are merged cells on this sheet." & vbCrLf & _
               "The macro will not work with merged cells.", vbOKOnly + vbCritical, "Macro will be Stopped"
        Exit Sub
    End If

    With ActiveSheet
        myLastRow = 0
        myLastCol = 0
        Set dummyRng = .UsedRange
        On Error Resume Next
        myLastRow = _
        .Cells.Find("*", after:=.Cells(1), _
                    LookIn:=xlFormulas, lookat:=xlWhole, _
                    searchdirection:=xlPrevious, _
                    searchorder:=xlByRows).Row
        myLastCol = _
        .Cells.Find("*", after:=.Cells(1), _
                    LookIn:=xlFormulas, lookat:=xlWhole, _
                    searchdirection:=xlPrevious, _
                    searchorder:=xlByColumns).Column
        On Error GoTo 0

        If myLastRow * myLastCol = 0 Then
            .Columns.Delete
        Else
            .Range(.Cells(myLastRow + 1, 1), _
                   .Cells(.Rows.Count, 1)).EntireRow.Delete
            .Range(.Cells(1, myLastCol + 1), _
                   .Cells(1, .Columns.Count)).EntireColumn.Delete
        End If
    End With

End Sub

Upvotes: 4

user9127093

Reputation: 11

I've used Jeeped solution and worked for me when i add .Activate, so:

With Worksheets("Sheet1")
        Debug.Print .UsedRange.Address(0, 0)
        .UsedRange.Clear
        .UsedRange   
        .Activate
        Debug.Print .UsedRange.Address(0, 0)
  End With

I'm using Excel2013

Upvotes: 1

Travis Dawson

Reputation: 56

This may or may not suit your data needs, but if your data is all in one contiguous block, you can use CurrentRegion instead of UsedRange, like this:

With Cells(1, 1).CurrentRegion
 MsgBox "I have " & .Rows.Count & " rows and " & .Columns.Count & " columns of data."
End With

Of course, if the region you care about does not start at cell A1, or if your sheet contains multiple contiguous regions that you care about, this option will not work. Depending on how predictable your data is, you can usually find at least one cell in each block of data, and once you have that, CurrentRegion will give you the range of the entire block.

Upvotes: 0

Claus

Reputation: 550

I double checked to make sure all the latests patches and service packs have been installed and they were.

I'm running Windows 10 and Excel 2016 version 16.0.6568.2034

I found that the range would only reset with the

ActiveSheet.UsedRange.Clear

And most importantly

ActiveWorkbook.Save

without the save command the range is not reset

Upvotes: -5

Claus

Reputation: 550

Here is how I inserted your code.

    Sheets("Edit Data").Select
'    Range("A6").Select
'    Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
'    Selection.Delete Shift:=xlUp
'    ActiveWorkbook.Save
    With Worksheets("Edit Data")
        Debug.Print .UsedRange.Address(0, 0)
        .UsedRange.Clear
        .UsedRange    '<~~ called by itself will reset it
        Debug.Print .UsedRange.Address(0, 0)
    End With

Here is the full used range with data

Here is the used range after your code executed

The end of range should be i7 instead it is still i26

However the code which I commented out does reset range to i7

From what you are saying just to confirm. My commented out code will only work for Excel 2010 and newer. We have some 2007 versions hanging around. For those the workbook will actually have to be closed and reopened for the range to reset?

Note- the code examples were executed on version 2016

Upvotes: 0

user4039065

Reputation:

If you call the Worksheet.UsedRange property by itself, it will reset.

    With Worksheets("Sheet1")
        Debug.Print .UsedRange.Address(0, 0)
        .UsedRange.Clear
        .UsedRange    '<~~ called by itself will reset it
        Debug.Print .UsedRange.Address(0, 0)
    End With

This extra step is unnecessary in xl2010 and above with all appropriate service packs installed.

Upvotes: 0

Excel resetting &quot;UsedRange&quot;

Answers (16)

Related Questions

Excel resetting "UsedRange"