This Page: www.cpearson.com/excel/LastCell.aspx

Last Updated: 06-Nov-2013

Copyright 1997 - 2014, Charles H. Pearson

Site Last Updated: 27-Dec-2016

Advanced Software

Design And Development

Design And Development

Office Integration Projects
NET Programming
XML Development

Search The Site:

Finding The Last Used Cell In A Range

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

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.

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