Getting the actual usedrange

I have a Excel worksheet that has a button.

开发者_Python百科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?

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.

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

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

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

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

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)

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

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.

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

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