FINDING the LAST ROW or COLUMN
General Information
A common requirement is to find the last cell in a row or column
within a sheet. You may have specific requirements where you want to find the
last row in a particular column or even the last column in a particular row
that has text values. There
may be obstacles in the way to finding the last cell such as having two sets
of data separated by blank rows and/or columns in between. This page will help
you select the option to achieve your goal.
VBA uses the same functionality as
keyboard shortcuts or menu options within Excel for navigation. To help with
understanding
I also give the closest keyboard shortcut & menu equivalent
under the heading 'Equivalent GUI Option' for each scenario. I
have also taken into account the importance of data areas and blank cells, so
I have included a section explaining their importance when determining the last
cell in a given situation.
Data Areas and Blank Cells
Data Areas, also called Range Areas, are collections of contiguous nonblank
cells. These data areas are separated by blank rows and/or columns in between.
In Example 1 below there are three areas  range A1:B5, D2:D3 and A7:A8.
Example 1: Presume this is the only data in the sheet.
A

B

C

D

E


1

100

600



2

200

700

1100


3

300

800

1200


4

400

900


5

500

1000


6


7

3500


8

4000


9

The importance of multiple areas is the way the END statement works which is equivalent to the keyboard shortcuts below. Excel will move to the first cell that meets the criteria, so if there are blank cells in between you may get unexpected results. If there are no nonblank cells from the active cell in the direction selected you may end up at the last column (IV) or row (65536) in the sheet depending upon the direction you selected. You will see you get varying results depending upon where the active cell is in relation to other data and whether there is blank rows/columns involved so take these two factors into account when determining the last cell. See the Traps to Avoid section for tips on avoiding problems.
Keyboard Shortcut  Description 
Result if A3 is the active cell

Result if B4 is the active cell

Result if D3 is the active cell

CTRL + UPARROW  From the active cell, move to the uppermost nonblank cell within the current column. 
A1

B1

D2

CTRL + DOWNARROW  From the active cell, move to the lowermost nonblank cell within the current column. 
A5

B5

D65536

CTRL + LEFTARROW  From the active cell, move to the leftmost
nonblank cell within the current row. 
A3

A4

B3

CTRL + RIGHTARROW  From the active cell, move to the rightmost nonblank cell within the current row. 
B3

IV4

IV3

Traps to Avoid
As noted in Data Areas and Blank Cells you may get unexpected
results depending upon the active cell you start with and the data in the target
range. Here are some useful suggestions in avoiding these.
Check for multiple areas in your target range
Take into account multiple areas in the range you are searching to find
the last row/column. The following code will tell you how many areas are in a
particular range. Note that the use of the Areas property on a range with
Special Cells enables the contents of the cells to be in subranges. If you used
Areas on the range without special cells the value of 1 is returned because
Excel is looking at the range addresses, not the contents.
Sub AreaCount()
'Determine the number of areas in a user defined range
Dim rngConstants As Range, rngFormulas As Range, rngAll As Range
Dim rngUser As Range
'Ask User for range to search
Set rngUser = Application.InputBox( _
prompt:="Select range to search." & vbLf & _
"Note: selecting one cell will return # areas in the whole Sheet", _
Title:="Area Count Selected Range", Type:=8)
'Set ranges for special types Constants and Formulas
On Error Resume Next 'If Constants or formulas dont exist
Set rngConstants = rngUser.SpecialCells(xlCellTypeConstants)
Set rngFormulas = rngUser.SpecialCells(xlCellTypeFormulas)
If rngConstants Is Nothing Then 'If there are no constants
Set rngAll = rngFormulas
ElseIf rngFormulas Is Nothing Then 'if there are no formulas
Set rngAll = rngConstants
Else 'Both constants & formulas may exist.
Set rngAll = Union(rngFormulas, rngConstants)
End If
'If only one cell is selected Excel returns total of all
'areas in the sheet.
If rngUser.Cells.Count = 1 Then
MsgBox prompt:="There is a total of " & rngAll.Areas.Count & _
" areas in the whole Sheet.", Title:="Number of Areas"
'A totally blank range will return nothing
ElseIf rngAll Is Nothing Then
MsgBox prompt:="There are 0 areas in your selected range.", _
Title:="Number of Areas"
Else
MsgBox prompt:="There are " & rngAll.Areas.Count & _
" areas in your selected range.", Title:="Number of Areas"
End If
End Sub
Row and Column Limits
Worksheets are limited to 65536 rows and 256 columns. If your target range has data
up to these maximums then the resulting End(xlUp) or End(xlToLeft) statements will result
in you moving to the first cell not the last.
Example 2: If every row in Column A has data
the following will result in 1 being returned, not 65536.
Range("A65536").End(xlUp).Row
Example 3: If every column in Row 2 has
data the following will result in 1 being returned, not 256.
Range("IV2").End(xlToLeft).Column
To avoid this issue, test for the last Row or Column containing
data before looking up the row or left of the column.
Example 4: Find the last row in column A and the last
column in row 23.
Dim LastRow as Long
If Range("A65536").Value = "" Then
LastRow = Range("A65536").End(xlUp).Row
Else
LastRow = 65536
End If
Dim LastColumn as Integer
If Range("IV23").Value = "" Then
LastColumn = Range("IV23").End(xlToLeft).Column
Else
LastColumn = 256
End If
Blank Columns or Rows
If the row or column that you are searching is completely blank (i.e. no
data), then the result will be row 1 or column A if you use the End(xlUp) or
End(xlToLeft) properties. Use the AreaCount procedure to determine if there is
any data in a particular range if you are unsure whether an area may be blank.
Scenarios
Select your requirement from one of the following scenarios.
Several options are provided for each scenario although there are probably many
more ways to achieve the same result. Note that when returning the column
values the results will be a number equivalent of a column not the letter. eg:
If the last used column is H then 8 would be returned. As noted above under
Traps to Avoid, you should test for the last row or column containing data in
conjunction with the techniques in these scenarios.
Last Row in a Sheet
Find the very last used row in the sheet regardless of column or the
number of data areas you have.
Equivalent GUI Option: Active cell can by any cell. From the Menu select EditGoTo...Special...Last
Cell or use keyboard shortcut Ctrl + End.
ActiveSheet.UsedRange.SpecialCells(xlLastCell).Row
or
Cells.SpecialCells(xlCellTypeLastCell).Row
Last Column in a Sheet
Find the very last used column in the sheet regardless of row
or the number of data areas you have.
Equivalent GUI Option: Active cell can by any cell. From the Menu select EditGoTo...Special...Last
Cell or use keyboard shortcut Ctrl + End.
ActiveSheet.UsedRange.SpecialCells(xlLastCell).Column
or
Cells.SpecialCells(xlCellTypeLastCell).Column
Last Row in One Column  One Area
Find the last row in a column where you have only one data area that
intersects with the target column. Note, if the target column was blank the
result would be the first row in that column.
Equivalent GUI Option: Active cell address is your
target column & row 65536. Use keyboard shortcut Ctrl + UpArrow.
Cells(65536,TargetColumn).End(xlUp).Row
Example 5: Find the last row in column A.
Range("A65536").End(xlUp).Row
or
Cells(65536,1).End(xlUp).Row
Last Row in One Column  Multiple
Areas
Find the last row in a column where multiple areas intersect with the target
column.
Equivalent GUI Option: Active cell address is your
target column & row is a row within your target area excluding the last row in
the area. Use keyboard shortcut Ctrl + DownArrow.
To find the last row use one of the following methods. My
personal preference is to use the first method with the first row in the target
area in case areas underneath the target area are changed.
1) Use target column and a row within the target area, that is not the last
row in the target area.
Cells(RowInTargetArea,TargetColumn).End(xlDown).Row
2) Use target column and a known blank row underneath your area or the first row in the following area.
Cells(BlankRowAfterTargetArea,TargetColumn).End(xlUp).Row
or
Cells(FirstRowInNextArea,TargetColumn).End(xlUp).Row
Example 6:
The following areas intersect with Column A:
Area One = A1:C500
Area Two = A700:F800
Area Three = A900:G5000
Find the last row in Area One...
Range("A300").End(xlDown).Row
or
Cells(1,1).End(xlDown).Row
or
Range("A600").End(xlUp).Row
or
Cells(700,1).End(xlUp).Row
Last Row in Multiple Columns
Find the last row over several columns where you have only one data area that
intersects with the target columns.
Equivalent GUI Option: None
Use Special cells (xlCellTypeLastCell) against the range where TargetRange is the multicolumn range
Range(TargetRange).SpecialCells(xlCellTypeLastCell).Row
OR
Use Find to locate the last row. TargetRange is the multicolumn range and FirstCellInRange is the first cell in the TargetRange.
Range(TargetRange).Find(What:="*", After:=FirstCellInRange, SearchOrder:=xlByRows,SearchDirection:=xlPrevious).Row
Example 7: Find the last row in columns C:F
Range(C:F).SpecialCells(xlCellTypeLastCell).Row
Last Column in One Row  One Area
Find the last column in a row where you have only one data area that
intersects with the target row. Note, if the target row was blank the
result would be column A in the target row.
Equivalent GUI Option: Active cell address is your
target row & column 256. Use keyboard shortcut Ctrl + LeftArrow.
Cells(TargetRow,256).End(xlToLeft).Column
Example 8: Find the last column in row 35.
Range("IV35").End(xlToLeft).Column
or
Cells(35, 256).End(xlToLeft).Column
Last Column in One Row  Multiple Areas
Find the last column in a row where multiple areas intersect with the target
row.
Equivalent GUI Option: Active cell address is your
target row & column is a column within your target area excluding the last
column in
the area. Use keyboard shortcut Ctrl + LeftArrow.
1) Use target column as the first column in your target target area and move to the right.
Cells(RowInTargetArea,FirstColumnInTargetArea).End(xlToRight).Column
2) Use target column and a known blank Column to the right of your area or the first column in the following area.
Cells(TargetRow,FirstColumnInNextArea).End(xlToLeft).Column
Example 9:
The following three areas intersect with Rows 1 to 100:
Area One = A1:C500
Area Two = F1:G100
Area Three = K1:R300
Find the last column in Area Two for row 100...
Range("F100").End(xlToRight).Column
or
Cells(100,6).End(xlToRight).Column
or
Range("K100").End(xlToLeft).Column
or
Cells(100,11).End(xlToLeft).Column
Last Column in Multiple Rows
Find the last column over several rows where you have only one data area that
intersects with the target rows.
Equivalent GUI Option: None
Use Special cells (xlCellTypeLastCell) against the range where TargetRange is the multirow range
Range(TargetRange).SpecialCells(xlCellTypeLastCell).Column
OR
Use Find to locate the last row. TargetRange is the multirow range and FirstCellInRange is the first cell in the TargetRange.
Range(TargetRange).Find(What:="*", After:=FirstCellInRange, SearchOrder:=xlByRows,SearchDirection:=xlPrevious).Column
Example 10: Find the last column in rows 510
Range(5:10).SpecialCells(xlCellTypeLastCell).Column