Excel Find Last Row Or Column of Worksheet
Learn some of the secrets to great Excel VBA programming!!
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
Important Note: Some of the methods give a "different" last row depending on hidden rows.
See the example at the end of the 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
' *********************************************************************
' Thanks to Daniel Pineault, 2010-2017 Microsoft MVP for this update:
lngLastRowSheet1 = shtSheet1.Cells(shtSheet1.Rows.Count, "A").End(xlUp).Row
lngLastRowSheet2 = shtSheet2.Cells(shtSheet2.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, shtSheet1.Columns.Count).End(xlToLeft).Column
lngLastColumnSheet2 = shtSheet2.Cells(6, shtSheet1.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:=xlByColumns, 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
' *********************************************************************
' Method 8 - Find Last Row Of Named Range
' *********************************************************************
Option Explicit
Public Sub LastRowOfNamedRange()
Dim lngLastRow As Long
lngLastRow = ActiveWorkbook.Names("MyNamedRange").RefersToRange _
.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
End Sub
' *********************************************************************
' CAUTION: The Last Row will be different when hidden rows are involved
' depending on which method you use. See The Code below:
' *********************************************************************
' ******************************************************************
'Let's say your data looks as follows, starting at Row 1:
'Null
'Null
'A
'B
'C
'D
'E
'F
'G
'
'The above represents Rows 1 through 9.
'Now, let's hide rows 5 through 20
'Each of the following three methods yields a different result
' ******************************************************************
Option Explicit
Public Sub TestOfLastLine()
Dim lngLastRowSheet1 As Long
' ************************************************************
' This Example Gives Results for a Worksheet With Data Only
' In Column A.
' Rows 1:2 contain no data
' Rows 3:9 have data
' Rows 10:MaxRows contain no data
' And Rows 5:20 are hidden
' ******************************************************************
' The Following Method Shows A Last Row of 4 (Ignores Hidden Rows)
' ******************************************************************
lngLastRowSheet1 = ActiveSheet.Cells(ActiveSheet.Rows.Count, "A").End(xlUp).Row
' ******************************************************************
' The Following Method Shows A Last Row of 20 (Counts Hidden Rows)
' ******************************************************************
lngLastRowSheet1 = ActiveSheet.UsedRange.Rows.Count
lngLastRowSheet1 = lngLastRowSheet1 + ActiveSheet.UsedRange.Row - 1
' ******************************************************************
' The Following Method Shows A Last Row of 9
' (Hidden Rows Have No Effect
' ******************************************************************
lngLastRowSheet1 = ActiveSheet.Cells.Find(What:="*", After:=[A1], SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
End Sub