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
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.
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).
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 complete code for GetLastCell is shown below:
Public Function GetLastCell(InRange As Range, SearchOrder As XlSearchOrder, _
Optional ProhibitEmptyFormula As Boolean = False) As Range
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
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. |