Getting the actual usedrange

Question

I have a Excel worksheet that has a button.

When I call the usedRange() function, the range it returns includes the button part.

Is there anyway I can just get actual used range that contains data?

score 36 · Accepted Answer · edited Jul 12 '12 at 18:38

36

What sort of button, neither a Forms Control nor an ActiveX control should affect the used range.

It is a known problem that excel does not keep track of the used range very well. Any reference to the used range via VBA will reset the value to the current used range. So try running this sub procedure:

Sub ResetUsedRng()
    Application.ActiveSheet.UsedRange 
End Sub

Failing that you may well have some formatting hanging round. Try clearing/deleting all the cells after your last row.

Regarding the above also see:

Excel Developer Tip

Another method to find the last used cell:

    Dim rLastCell As Range

    Set rLastCell = ActiveSheet.Cells.Find(What:="*", After:=.Cells(1, 1), LookIn:=xlFormulas, LookAt:= _
    xlPart, SearchOrder:=xlByRows, SearchDirection:=xlPrevious, MatchCase:=False)

Change the search direction to find the first used cell.

edited Jul 12 '12 at 18:38

davidlandy

61
1
3

answered Sep 14 '11 at 21:33

Reafidy

8,240
5
51
83

I will try this out. I ended up wrote my own search function. Basically, if I find an empty cell, I count up the next 100 row. If the total is 0, I assume it is the last row. – thinkanotherone Sep 15 '11 at 17:41
Make sure you have `ActiveSheet.AutoFilterMode=False` before calling `Find`, or some rows with actual content will not be taken into account. – Andy Jan 11 '20 at 08:28
No. Sheet.UsedRange does not reliably reset it. It simply goes wrong sometimes and stays wrong. This did seem to get better about Excel 2007. – Tuntable Jun 16 '20 at 00:41
... but can still go wrong particularly if there are pivot tables. – Tuntable Jun 16 '20 at 00:59
at least in Excel 2016 the .Find command skips merged cells, when used on the .Cells range, which is unfortunate, when they are at the border of the used range. – j-hap Feb 10 '21 at 08:54
This is a thorough answer. Thank you for taking the time. God bless! – Ejaz Ahmed Nov 21 '21 at 12:30

JMax · Answer 2 · 2012-09-13T07:22:33.137

Readify made a very complete answer. Yet, I wanted to add the End statement, you can use:

Find the last used cell, before a blank in a Column:

Sub LastCellBeforeBlankInColumn()
Range("A1").End(xldown).Select
End Sub

Find the very last used cell in a Column:

Sub LastCellInColumn()
Range("A" & Rows.Count).End(xlup).Select
End Sub

Find the last cell, before a blank in a Row:

Sub LastCellBeforeBlankInRow()
Range("A1").End(xlToRight).Select
End Sub

Find the very last used cell in a Row:

Sub LastCellInRow()
Range("IV1").End(xlToLeft).Select
End Sub

See here for more information (and the explanation why xlCellTypeLastCell is not very reliable).

+1 Great info. I would suggest using Range("A" & Rows.Count).End(xlup) that way your code is realiable for multiple versions of excel. — Reafidy, Sep 15 '11 at 10:05

davidlandy · Answer 3 · 2012-07-12T09:18:59.960

Here's a pair of functions to return the last row and col of a worksheet, based on Reafidy's solution above.

    Function LastRow(ws As Object) As Long

        Dim rLastCell As Object
        On Error GoTo ErrHan
        Set rLastCell = ws.Cells.Find("*", ws.Cells(1, 1), , , xlByRows, _
                                      xlPrevious)
        LastRow = rLastCell.Row

    ErrExit:
        Exit Function

    ErrHan:
        MsgBox "Error " & Err.Number & ": " & Err.Description, _
               vbExclamation, "LastRow()"
        Resume ErrExit

    End Function

    Function LastCol(ws As Object) As Long

        Dim rLastCell As Object
        On Error GoTo ErrHan
        Set rLastCell = ws.Cells.Find("*", ws.Cells(1, 1), , , xlByColumns, _
                                      xlPrevious)
        LastCol = rLastCell.Column

    ErrExit:
        Exit Function

    ErrHan:
        MsgBox "Error " & Err.Number & ": " & Err.Description, _
               vbExclamation, "LastRow()"
        Resume ErrExit

    End Function

Very nice. I had used a binary search, but this is simpler and should be much faster. However you probably also want to specify Lookin:=xlFormulas; A formula might return "" — Tuntable, Jun 16 '20 at 00:44
Actually no, "" matches "*", and is different from isempty(). — Tuntable, Jun 16 '20 at 01:00

score 4 · Answer 4 · answered Jan 30 '20 at 04:44

This function returns the actual used range to the lower right limit. It returns "Nothing" if the sheet is empty.

'2020-01-26
Function fUsedRange() As Range
Dim lngLastRow As Long
Dim lngLastCol As Long
Dim rngLastCell As Range
    On Error Resume Next
    Set rngLastCell = ActiveSheet.Cells.Find("*", searchorder:=xlByRows, searchdirection:=xlPrevious)
    If rngLastCell Is Nothing Then  'look for data backwards in rows
        Set fUsedRange = Nothing
        Exit Function
    Else
        lngLastRow = rngLastCell.Row
    End If
    Set rngLastCell = ActiveSheet.Cells.Find("*", searchorder:=xlByColumns, searchdirection:=xlPrevious)
    If rngLastCell Is Nothing Then  'look for data backwards in columns
        Set fUsedRange = Nothing
        Exit Function
    Else
        lngLastCol = rngLastCell.Column
    End If
    Set fUsedRange = ActiveSheet.Range(Cells(1, 1), Cells(lngLastRow, lngLastCol))  'set up range
End Function

score 3 · Answer 5 · edited Aug 07 '12 at 13:58

Public Sub FindTrueUsedRange(RowLast As Long, ColLast As Long)
    Application.EnableEvents = False
    Application.ScreenUpdating = False
    RowLast = 0
    ColLast = 0
    ActiveSheet.UsedRange.Select
    Cells(1, 1).Activate
    Selection.End(xlDown).Select
    Selection.End(xlDown).Select
    On Error GoTo -1: On Error GoTo Quit
    Cells.Find(What:="*", LookIn:=xlFormulas, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Activate
    On Error GoTo -1: On Error GoTo 0
    RowLast = Selection.Row
    Cells(1, 1).Activate
    Selection.End(xlToRight).Select
    Selection.End(xlToRight).Select
    Cells.Find(What:="*", LookIn:=xlFormulas, LookAt:=xlWhole, SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Activate
    ColLast = Selection.Column
Quit:
    Application.ScreenUpdating = True
    Application.EnableEvents = True
    On Error GoTo -1: On Error GoTo 0
End Sub

score 1 · Answer 6 · answered Dec 04 '15 at 19:52

I use the following vba code to determine the entire used rows range for the worksheet to then shorten the selected range of a column:

    Set rUsedRowRange = Selection.Worksheet.UsedRange.Columns( _
    Selection.Column - Selection.Worksheet.UsedRange.Column + 1)

Also works the other way around:

    Set rUsedColumnRange = Selection.Worksheet.UsedRange.Rows( _
    Selection.Row - Selection.Worksheet.UsedRange.Row + 1)

score 0 · Answer 7 · answered Jun 20 '19 at 15:55

This function gives all 4 limits of the used range:

Function FindUsedRangeLimits()
    Set Sheet = ActiveSheet
    Sheet.UsedRange.Select

    ' Display the range's rows and columns.
    row_min = Sheet.UsedRange.Row
    row_max = row_min + Sheet.UsedRange.Rows.Count - 1
    col_min = Sheet.UsedRange.Column
    col_max = col_min + Sheet.UsedRange.Columns.Count - 1

    MsgBox "Rows " & row_min & " - " & row_max & vbCrLf & _
           "Columns: " & col_min & " - " & col_max
    LastCellBeforeBlankInColumn = True
End Function

The definition of the UsedRange does also include formatted cells, even when they are empty. Most of the times I need the UsedRange defined as non empty cells. — j-hap, Feb 10 '21 at 08:51

score 0 · Answer 8 · answered Jun 17 '20 at 10:09

Timings on Excel 2013 fairly slow machine with a big bad used range million rows:

26ms Cells.Find xlPrevious method (as above)

0.4ms Sheet.UsedRange (just call it)

0.14ms Counta binary search + 0.4ms Used Range to start search (12 CountA calls)

So the Find xlPrevious is quite slow if that is of concern.

The CountA binary search approach is to first do a Used Range. Then chop the range in half and see if there are any non-empty cells in the bottom half, and then halve again as needed. It is tricky to get right.

score 0 · Answer 9 · answered Feb 10 '21 at 08:47

Here's another one. It looks for the first and last non empty cell and builds are range from those. This also handles cases where your data is not rectangular and does not start in A1. Furthermore it handles merged cells as well, which .Find skips when executed from a macro, used on .Cells on a worksheet.

Function getUsedRange(ByRef sheet As Worksheet) As Range
' finds used range by looking for non empty cells
' works around bug in .Find that skips merged cells
' by starting at with the UsedRange (that may be too big)

' credit to https://contexturesblog.com/archives/2012/03/01/select-actual-used-range-in-excel-sheet/
' for the .Find commands

Dim excelsUsedRange As Range
Dim lastRow As Long
Dim lastCol As Long
Dim lastCell As Range
Dim firstRow As Long
Dim firstCol As Long
Dim firstCell As Range

Set excelsUsedRange = ActiveSheet.UsedRange

lastRow = excelsUsedRange.Find(What:="*", _
                               LookIn:=xlValues, SearchOrder:=xlRows, _
                               SearchDirection:=xlPrevious).Row
lastCol = excelsUsedRange.Find(What:="*", _
                               LookIn:=xlValues, SearchOrder:=xlByColumns, _
                               SearchDirection:=xlPrevious).Column
Set lastCell = sheet.Cells(lastRow, lastCol)

firstRow = excelsUsedRange.Find(What:="*", After:=lastCell, _
                                LookIn:=xlValues, SearchOrder:=xlRows, _
                                SearchDirection:=xlNext).Row
firstCol = excelsUsedRange.Find(What:="*", After:=lastCell, _
                                LookIn:=xlValues, SearchOrder:=xlByColumns, _
                                SearchDirection:=xlNext).Row
Set firstCell = sheet.Cells(firstRow, firstCol)
Set getUsedRange = sheet.Range(firstCell, lastCell)
End Function

Greedo · Answer 10 · 2023-02-06T16:52:08.843

This is a different approach to the other answers, which will give you all the regions with data - a Region is something enclosed by an empty row and column and or the the edge of the worksheet. Basically it gives all the rectangles of data:

Public Function ContentRange(ByVal ws As Worksheet) As Range

    'First, identify any cells with data, whose neighbourhood we will inspect
    ' to identify contiguous regions of content
    'For efficiency, restrict our search to only the UsedRange
    ' NB. This may be pointless if .SpecialCells does this internally already, it probably does...
    With ws.UsedRange 'includes data and cells that have been formatted
        Dim cellsWithContent As Range
        On Error Resume Next '.specialCells will error if nothing found, we can ignore it though
        Set cellsWithContent = .SpecialCells(xlCellTypeConstants)
        Set cellsWithContent = Union(cellsWithContent, .SpecialCells(xlCellTypeFormulas))
        On Error GoTo 0
    End With
    'Early exit; return Nothing if there is no Data
    If cellsWithContent Is Nothing Then Exit Function
    
    'Next, loop over all the content cells and group their currentRegions
    ' This allows us to include some blank cells which are interspersed amongst the data
    ' It is faster to loop over areas rather than cell by cell since we merge all the CurrentRegions either way
   
    Dim item As Range
    Dim usedRegions As Range
    For Each item In cellsWithContent.Areas
        'Debug.Print "adding: "; item.Address, item.CurrentRegion.Address
        If usedRegions Is Nothing Then
            Set usedRegions = item.CurrentRegion 'expands "item" to include any surrounding non-blank data
        Else
            Set usedRegions = Union(usedRegions, item.CurrentRegion)
        End If
    Next item
    'Debug.Print cellsWithContent.Address; "->"; usedRegions.Address
    Set ContentRange = usedRegions
End Function

Used like:

Debug.Print ContentRange(Sheet1).Address '$A$1:$F$22
Debug.Print ContentRange(Sheet2).Address '$A$1:$F$22,$N$5:$M$7

The result is a Range object containing 1 or more Areas, each of it which will represent a data/formula containing region on the sheet.

It is the same technique as clicking in all the cells in your sheet and pressing Ctrl+T, merging all those areas. I'm using it to find potential tables of data

Getting the actual usedrange

10 Answers10

Linked