Find last row, column or last cell

Ron de Bruin (last update 5-May-2008)
Go back to the Excel tips page

You must copy all code on this page in a standard module.
http://www.rondebruin.nl/code.htm

Last used cell in one row or one column

The macro's give you the row or column number of the last cell with data in one row or one column.
Note: This code below will not work correct if the last row with data is hidden.

Sub LastRowInOneColumn()
'Find the last used row in a Column: column A in this example
    Dim LastRow As Long
    With ActiveSheet
        LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
    End With
    MsgBox LastRow
End Sub


Sub LastColumnInOneRow()
'Find the last used column in a Row: row 1 in this example
    Dim LastCol As Integer
    With ActiveSheet
        LastCol = .Cells(1, .Columns.Count).End(xlToLeft).Column
    End With
    MsgBox LastCol
End Sub

Last used cell in a worksheet

Possible problems with xlCellTypeLastCell and UsedRange are:

The last cell will only re-set when you save (or save/close/reopen the file).
If cell formatting is changed it will not reset the last cell, clearing the data is not
enough, you must delete the rows or columns then,
See: http://www.contextures.com/xlfaqApp.html#Unused

So when using VBA you cannot rely on this two macros if you want the last row
or column with data on your worksheet.

Last Row :

Sub xlCellTypeLastCell_Example_Row()
    Dim LastRow As Long
    With ActiveSheet
        LastRow = .Range("A1").SpecialCells(xlCellTypeLastCell).Row
    End With
    MsgBox LastRow
End Sub


Sub UsedRange_Example_Row()
    Dim LastRow As Long
    With ActiveSheet.UsedRange
        LastRow = .Rows(.Rows.Count).Row
    End With
    MsgBox LastRow
End Sub

Last Column :

Sub xlCellTypeLastCell_Example_Column()
    Dim LastColumn As Long
    With ActiveSheet
        LastColumn = .Range("A1").SpecialCells(xlCellTypeLastCell).Column
    End With
    MsgBox LastColumn
End Sub


Sub UsedRange_Example_Column()
    Dim LastColumn As Long
    With ActiveSheet.UsedRange
        LastColumn = .Columns(.Columns.Count).Column
    End With
    MsgBox LastColumn
End Sub

Use the "Last" function to Find the last row, column
or cell in range or worksheet

In the example macros we call the function Last and this function have two arguments

Argument 1 can be 1, 2 or 3
1 = last row
2 = last column
3 = last cell
Argument 2 is the range where you want to search in

Copy the example macros together with the function "Last" in a normal module of your workbook.

Sub LastRow_Example()
    Dim LastRow As Long
    Dim rng As Range

    ' Use all cells on the sheet
    Set rng = Sheets("Sheet1").Cells

    'Use a range on the sheet
    'Set rng = Sheets("Sheet1").Range("A1:D30")

    ' Find the last row
    LastRow = Last(1, rng)

    ' After the last row with data change the value of the cell in Column A
    rng.Parent.Cells(LastRow + 1, 1).Value = "Hi there"

End Sub


Sub LastColumn_Example()
    Dim LastCol As Long
    Dim rng As Range

    ' Use all cells on the sheet
    Set rng = Sheets("Sheet1").Cells

    'Or use a range on the sheet
    'Set rng = Sheets("Sheet1").Range("A1:D30")

    ' Find the last column
    LastCol = Last(2, rng)

    ' After the last column with data change the value of the cell in row 1
    rng.Parent.Cells(1, LastCol + 1).Value = "Hi there"

End Sub


Sub LastCell_Example()
    Dim LastCell As String
    Dim rng As Range

    ' Use all cells on the sheet
    Set rng = Sheets("Sheet1").Cells

    'Or use a range on the sheet
    'Set rng = Sheets("Sheet1").Range("A1:D30")

    ' Find the last cell
    LastCell = Last(3, rng)

    ' Select from A1 till the last cell in Rng
    With rng.Parent
        .Select
        .Range("A1", LastCell).Select
    End With
End Sub

'This is the function we use in the macro's above

Function Last(choice As Long, rng As Range)
'Ron de Bruin, 5 May 2008
' 1 = last row
' 2 = last column
' 3 = last cell
    Dim lrw As Long
    Dim lcol As Long

    Select Case choice

    Case 1:
        On Error Resume Next
        Last = rng.Find(What:="*", _
                        After:=rng.Cells(1), _
                        Lookat:=xlPart, _
                        LookIn:=xlFormulas, _
                        SearchOrder:=xlByRows, _
                        SearchDirection:=xlPrevious, _
                        MatchCase:=False).Row
        On Error GoTo 0

    Case 2:
        On Error Resume Next
        Last = rng.Find(What:="*", _
                        After:=rng.Cells(1), _
                        Lookat:=xlPart, _
                        LookIn:=xlFormulas, _
                        SearchOrder:=xlByColumns, _
                        SearchDirection:=xlPrevious, _
                        MatchCase:=False).Column
        On Error GoTo 0

    Case 3:
        On Error Resume Next
        lrw = rng.Find(What:="*", _
                       After:=rng.Cells(1), _
                       Lookat:=xlPart, _
                       LookIn:=xlFormulas, _
                       SearchOrder:=xlByRows, _
                       SearchDirection:=xlPrevious, _
                       MatchCase:=False).Row
        On Error GoTo 0

        On Error Resume Next
        lcol = rng.Find(What:="*", _
                        After:=rng.Cells(1), _
                        Lookat:=xlPart, _
                        LookIn:=xlFormulas, _
                        SearchOrder:=xlByColumns, _
                        SearchDirection:=xlPrevious, _
                        MatchCase:=False).Column
        On Error GoTo 0

        On Error Resume Next
        Last = rng.Parent.Cells(lrw, lcol).Address(False, False)
        If Err.Number > 0 Then
            Last = rng.Cells(1).Address(False, False)
            Err.Clear
        End If
        On Error GoTo 0

    End Select
End Function

Caveats:

1: Tom Ogilvy posted this in the newsgroup
Find does not find a cell containing the Null string "" entered when
you do Edit=>Copy and then Edit=>Paste Special, Values for a cell containing
a formula like =IF(A1="","",A1*1.19), which may or may not be desirable (end(xlup) does).

2 : Find not see cell formatting but only data, if this is important for you see the
xlCellTypeLastCell and UsedRange section of this page to find the last row or column.

3: If you use merged cells (Please do not use merged cells) maybe you get unexpected results.
It will give you the column number of the first cell and not the last cell in the merged cells.