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?

Answer 1

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

Answer 2

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

Answer 3

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.

Answer 4

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

Answer 5

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)