FINDING the LAST ROW or COLUMN

  1. General Information
  2. Data Areas and Blank Cells
  3. Traps to Avoid
  4. Scenarios
       Last Row in a Sheet
       Last Column in a Sheet

       Last Row in One Column - One Area
       Last Row in One Column - Multiple Areas
       Last Row in Multiple Columns
       Last Column in One Row - One Area
       Last Column in One Row - Multiple Areas
       Last Column in Multiple Rows


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 non-blank 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 non-blank 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 non-blank cell within the current column.
A1
B1
D2
CTRL + DOWNARROW From the active cell, move to the lowermost non-blank cell within the current column.
A5
B5
D65536
CTRL + LEFTARROW From the active cell, move to the leftmost
non-blank cell within the current row.
A3
A4
B3
CTRL + RIGHTARROW From the active cell, move to the rightmost non-blank 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 sub-ranges. 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 Edit|GoTo...|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 Edit|GoTo...|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 multi-column range

Range(TargetRange).SpecialCells(xlCellTypeLastCell).Row

OR

Use Find to locate the last row. TargetRange is the multi-column 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 multi-row range

Range(TargetRange).SpecialCells(xlCellTypeLastCell).Column

OR

Use Find to locate the last row. TargetRange is the multi-row 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 5-10

Range(5:10).SpecialCells(xlCellTypeLastCell).Column