Finding The Last Used Cell In A Range

This page describes how to find the last used cell in a range.

Last Cell Of Row Or Column

It is quite simple to get the last used in a single row or column. You do this by emulating the behavior of the END key and one of the arrow keys. For example, to get the last used cell in column C on Sheet1, use code like

''''''''''''''''''''''''''''''''''''''
' Last cell in column
''''''''''''''''''''''''''''''''''''''
Dim WS As Worksheet
Dim LastCell As Range
Dim LastCellRowNumber As Long

Set WS = Worksheets("Sheet1")
With WS
    Set LastCell = .Cells(.Rows.Count, "C").End(xlUp)
    LastCellRowNumber = LastCell.Row
End With

This works by going to the last row of the worksheet in column C and then uses End to scan upwards until a non-blank cell is encountered. There may well be empty cells above this cell, but this is the last used cell in the column. The Range variable LastCell references the last cell. The Long variable LastCellRowNumber contains the row number of the last cell in the column.

You can use a formula to return the row number of the last non-blank cell. In this case, the last used cell is one that contains anything that is not an empty string. If a formula returns an empty string, that is ignored.

=MAX(ROW(A1:A100)*(A1:A100<>""))

Change the instances of 100 to a row that is after any possible data. This is an array formula, so you must press CTRL SHIFT ENTER rather than just ENTER when you first enter the formula and whenever you edit it later.

The code for finding the last used cell in a row is nearly identical.

''''''''''''''''''''''''''''''''''''''
' Last cell in row
''''''''''''''''''''''''''''''''''''''
Dim WS As Worksheet
Dim LastCell As Range
Dim LastCellColumnNumber As Long
Dim RowNumber As Long
Set WS = Worksheets("Sheet1")
With WS
    RowNumber = 2
    If .Cells(RowNumber, .Columns.Count) <> vbNullString Then
        Set LastCell = .Cells(RowNumber, .Columns.Count)
        LastCellColumnNumber = LastCell.Column
    Else
        Set LastCell = .Cells(RowNumber, .Columns.Count).End(xlToLeft)
        LastCellColumnNumber = LastCell.Column
    End If
End With

where RowNum is the row number to test. You can use an array formula to get the last used column in a row:

=MAX(COLUMN(A:IV)*(A1:IV1<>""))

In both of the VBA code snippets, the last cell may appear to be blank if it contains a formula that evaluates to an empty string. The formulas treat a cell that contains a formula that returns an empty string to be empty, so the last used cell may be above or to the left of a cell with such a formula.

Which Last Cell On Worksheet

In order to get the last cell in a range, you first need to think about what constitutes the "last cell". If you search row-by-row, moving across one row then down to the next, the last cell is the right-most cell in the last row than contains any values. If you search column-by-column, moving down one column then moving across to the next, the last cell is the lower-most cell in the last column that contains a non-empty element. For example,in the following range of cells:

the value g is the last entry when searching on a row-by-row basis. On the other hand, the value e is the last entry when searching column-by-column. Which is the "true" last cell depends on the context of the data.

The VBA code SpecialCells(xlCellTypeLastCell) scans row by row to find the last cell, so in the table above, the cell containing the value g would be returned as the last cell. There is no way to change the way that SpecialCells(xlCellTypeLastCell) determines the last cell.

With some VBA, we can find the last cell on either a row basis or a column basis. The code below works by calling the Find method on the specified range, with settings to search for any non-blank content (the * in the what parameter), and scanning from the end of the range backwards to the start of the range (the xlPrevious value of the SearchDirection parameter).

You can download the file with all the example code on this page.

The GetLastCell procedure is used to find the last cell and has the declaration:

Public Function GetLastCell(InRange As Range, SearchOrder As XlSearchOrder, _
                        Optional ProhibitEmptyFormula As Boolean = False) As Range

The InRange is the range of which the last cell should be found. The SearchOrder parameter is either xlByColumns or xlByRows. If SearchOrder is any value other than xlByRows, xlByColumns, or xlByRows + xlByColumns (see below), the code raises an error 5 (Invalid procedure call or argument). The ProhibitEmptyFormula indicates how the code should treat a cell that contains a formula that evaluates to an empty string. If this parameter is omitted or False, the last cell is allowed to be a cell containing a formula that evaluates to an empty string. If this value is True, then the last cell must be a cell containing a static constant or a formula that does not evaluate to an empty string.

The SearchOrder can also take the value xlByColumns + xlByColumns which makes the last cell the intersection of the row containing the last cell on a row-by-row basis and the column of the column containing the last cell on a column-by-column basis. This cell may be empty. In the example above, this cell is the cell immediately below the value e.

If you want the absolute last cell of a range, regardless of whether it has any content, you can use the simple code:

    Dim RR As Range
    Dim LastCell As Range
    Set RR = Range("A1:C10")
    Set LastCell = RR(RR.Cells.Count)

The GetLastCell Code

The complete code for GetLastCell is shown below:

You can download the file with all the example code on this page.

Public Function GetLastCell(InRange As Range, SearchOrder As XlSearchOrder, _
                        Optional ProhibitEmptyFormula As Boolean = False) As Range
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' GetLastCell
' By Chip Pearson, chip@cpearson.com, www.cpearson.com
'
' This returns the last used cell in a worksheet or range. If InRange
' is a single cell, the last cell of the entire worksheet if found. If
' InRange contains two or more cells, the last cell in that range is
' returned.
' If SearchOrder is xlByRows (= 1), the last cell is the last
' (right-most) non-blank cell on the last row of data in the
' worksheet's UsedRange. If SearchOrder is xlByColumns
' (= 2), the last cell is the last (bottom-most) non-blank cell in the
' last (right-most) column of the worksheet's UsedRange. If SearchOrder
' is xlByColumns + xlByRows (= 3), the last cell is the intersection of
' the last row and the last column. Note that this cell may not contain
' any value.
' If SearchOrder is anything other than xlByRows, xlByColumns, or
' xlByRows+xlByColumns, an error 5 is raised.
'
' ProhibitEmptyFormula indicates how to handle the case in which the
' last cell is a formula that evaluates to an empty string. If this setting
' is omitted for False, the last cell is allowed to be a formula that
' evaluates to an empty string. If this setting is True, the last cell
' must be either a static value or a formula that evaluates to a non-empty
' string. The default is False, allowing the last cell to be a formula
' that evaluates to an empty string.
'''''''''''''''''''''''''
' Example:
'       a   b   c
'               d   e
'       f   g
'
' If SearchOrder is xlByRows, the last cell is 'g'. If SearchOrder is
' xlByColumns, the last cell is 'e'. If SearchOrder is xlByRows+xlByColumns,
' the last cell is the intersection of the row containing 'g' and the column
' containing 'e'. This cell has no value in this example.
'
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Dim WS As Worksheet
Dim R As Range
Dim LastCell As Range
Dim LastR As Range
Dim LastC As Range
Dim SearchRange As Range
Dim LookIn As XlFindLookIn
Dim RR As Range

Set WS = InRange.Worksheet

If ProhibitEmptyFormula = False Then
    LookIn = xlFormulas
Else
    LookIn = xlValues
End If

Select Case SearchOrder
    Case XlSearchOrder.xlByColumns, XlSearchOrder.xlByRows, _
            XlSearchOrder.xlByColumns + XlSearchOrder.xlByRows
        ' OK
    Case Else
        Err.Raise 5
        Exit Function
End Select

With WS
    If InRange.Cells.Count = 1 Then
        Set RR = .UsedRange
    Else
       Set RR = InRange
    End If
    Set R = RR(RR.Cells.Count)
    
    If SearchOrder = xlByColumns Then
        Set LastCell = RR.Find(what:="*", after:=R, LookIn:=LookIn, _
                LookAt:=xlPart, SearchOrder:=xlByColumns, _
                SearchDirection:=xlPrevious, MatchCase:=False)
    ElseIf SearchOrder = xlByRows Then
        Set LastCell = RR.Find(what:="*", after:=R, LookIn:=LookIn, _
                LookAt:=xlPart, SearchOrder:=xlByRows, _
                SearchDirection:=xlPrevious, MatchCase:=False)
    ElseIf SearchOrder = xlByColumns + xlByRows Then
        Set LastC = RR.Find(what:="*", after:=R, LookIn:=LookIn, _
                LookAt:=xlPart, SearchOrder:=xlByColumns, _
                SearchDirection:=xlPrevious, MatchCase:=False)
        Set LastR = RR.Find(what:="*", after:=R, LookIn:=LookIn, _
                LookAt:=xlPart, SearchOrder:=xlByRows, _
                SearchDirection:=xlPrevious, MatchCase:=False)
        Set LastCell = Application.Intersect(LastR.EntireRow, LastC.EntireColumn)
    Else
        Err.Raise 5
        Exit Function
    End If
End With

Set GetLastCell = LastCell

End Function
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' END CODE GetLastCell
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

This page last updated: 20-August-2008.