Excel Find Last Row Or Column of Worksheet
Almost every Excel application needs to find the last row or column of a worksheet that contains data. Once the number of rows is determined, looping structures can be initiated to process the cell contents. The code below lists several ways to accomplish this goal. Read the notes in the program code to determine the action being taken. Some of the functions require a specific row or column to be searched; others look at the entire worksheet to make a determination.
Program Code
Option Explicit
Public Function TestLastColumnCode()
Dim lngLastColumnSheet1 As Long
Dim lngLastColumnSheet2 As Long
Dim lngLastRowSheet1 As Long
Dim lngLastRowSheet2 As Long
Dim shtSheet1 As Worksheet
Dim shtSheet2 As Worksheet
Set shtSheet1 = Sheets("Sheet1")
Set shtSheet2 = Sheets("Sheet2")
shtSheet1.Activate
' *********************************************************************
' Method 1 Locate The Absolute Last Row Of A Worksheet
' *********************************************************************
lngLastRowSheet1 = ActiveSheet.UsedRange.Rows.Count
lngLastRowSheet1 = lngLastRowSheet1 + ActiveSheet.UsedRange.Row - 1
lngLastRowSheet2 = shtSheet2.UsedRange.Rows.Count
lngLastRowSheet2 = lngLastRowSheet2 + shtSheet2.UsedRange.Row - 1
' *********************************************************************
' Method 2 Locate The Last Row Of A Specific Column
' *********************************************************************
lngLastRowSheet1 = shtSheet1.Cells(Rows.Count, "A").End(xlUp).Row
lngLastRowSheet2 = shtSheet2.Cells(Rows.Count, "B").End(xlUp).Row
lngLastRowSheet1 = shtSheet1.Range("A" & Rows.Count).End(xlUp).Row
lngLastRowSheet2 = shtSheet2.Range("B" & Rows.Count).End(xlUp).Row
' *********************************************************************
' Method 3 Use The Find Command To Find The Absolute Last Row
' *********************************************************************
lngLastRowSheet1 = shtSheet1.Cells.Find(What:="*", After:=[A1], SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
lngLastRowSheet2 = shtSheet2.Cells.Find(What:="*", After:=[A1], SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
' *********************************************************************
' Method 4 Locate The Last Column Of A Specific Row
' *********************************************************************
lngLastColumnSheet1 = shtSheet1.Cells(9, Columns.Count).End(xlToLeft).Column
lngLastColumnSheet2 = shtSheet2.Cells(6, Columns.Count).End(xlToLeft).Column
' *********************************************************************
' Method 5 Use The Find Command To Find The Absolute Last Row
' *********************************************************************
lngLastRowSheet1 = LastRow(shtSheet1)
lngLastRowSheet2 = LastRow(shtSheet2)
' *********************************************************************
' Method 6 Use The Find Command To Find The Absolute Last Column
' *********************************************************************
lngLastColumnSheet1 = LastColumn(shtSheet1)
lngLastColumnSheet2 = LastColumn(shtSheet2)
End Function
Public Function LastRow(Optional wks As Worksheet) As Long
If wks Is Nothing Then Set wks = ActiveSheet
LastRow = wks.Cells.Find(What:="*", _
After:=wks.Range("A1"), _
Lookat:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Row
End Function
Public Function LastColumn(Optional wks As Worksheet) As Long
If wks Is Nothing Then Set wks = ActiveSheet
LastColumn = wks.Cells.Find(What:="*", _
After:=wks.Range("A1"), _
Lookat:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByColumns, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Column
End Function
' *********************************************************************
' Method 7 Other Approaches Courtesy of Sidarth Rout
' *********************************************************************
To find the last row which has data use this.
Sheets("Sheet1").Cells.Find(What:="*", After:=[A1], SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
To find the last Column which has data use this.
Sheets("Sheet1").Cells.Find(What:="*", After:=[A1], SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Column
To find the last cell which has data use this.
Sheets("Sheet1").Cells.Find(What:="*", After:=[A1], SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Address
