How to Find the Last Row and Column in VBA

1. Code Syntax
2. Get the Last Row/Column With Data in the Current Worksheet in VBA
3. Get the Last Row/Column With Data From Another Worksheet in VBA
4. Get the Last Row/Column of a Worksheet Using a Function

When dealing with data in a spreadsheet, we need to know the last row and last column with data. It is useful to set a limit where our cursor can iterate.

VBA does not have a built-in function to return the last row/column used in a spreadsheet. But with the use of simple VBA solutions, we can make our user-defined function designed to return the value of the last row/column used in a worksheet.

This article will demonstrate how to find the last row and column used in a spreadsheet using Excel VBA.

Code Syntax

Code to get the last row:

Cells(Rows.Count,[colNum]).End(xlUp).Row

code to get the last column:

Cells(Rows.Count,[rowNum]).End(xlUp).Row

Where,

Get the Last Row/Column With Data in the Current Worksheet in VBA

The code block below needs to be written on the target worksheet on Microsoft Excel Objects.

Sub GetLastRowCol()

'Declare lastRow and Last Col as Long datatype as we need to presume the values will be _
assigned with these variables are very high numbers.
Dim lastRow as Long
Dim lastCol as Long

'Assigning lastRow and lastCol variable
lastRow = Cells(Rows.Count, 1).End(xlUp).Row
lastCol = Cells(1, Columns.Count).End(xlToLeft).Column
Debug.Print "The last Row is: " & lastRow & vbNewline & "The last Column is :" & lastCol 
End Sub

Output:

The last Row is :1
The last Column is :1

Get the Last Row/Column With Data From Another Worksheet in VBA

When dealing with several worksheets, we need to explicitly assign which worksheet we are currently in.

The code block below will demonstrate how to get the last row/column used from a different worksheet.

Sub GetLastRowCol()

'Declare lastRow and Last Col as Long datatype as we need to presume the values will be _
assigned with these variables are very high numbers.
Dim lastRow as Long
Dim lastCol as Long

'Declaring workbook and worksheets
Dim wb as Workbook
Dim s1, s2 as Worksheet

'Initializing the wb, s1, and s2 objects.
Set wb = ThisWorkBook
Set s1 = wb.Sheets("Sheet1")
Set s2 = wb.Sheets("Sheet2")

'Will return the row number of the last row in Column A on Sheet1
lastRow = s1.Cells(s1.Rows.Count, 1).End(xlUp).Row
'Will return the row number of the last row in Row 1 on Sheet1
lastCol = s1.Cells(1, s1.Columns.Count).End(xlToLeft).Column

Debug.print "The last row in Sheet1:" & lastRow & vbNewline & "The last column in Sheet 1:" & lastCol

'Will return the row number of the last row in Column A on Sheet2
lastRow = s2.Cells(s2.Rows.Count, 1).End(xlUp).Row
'Will return the row number of the last row in Column A on Sheet2
lastCol = s2.Cells(1, s2.Columns.Count).End(xlToLeft).Column

Debug.print "The last row in Sheet2:" & lastRow & vbNewline & "The last column in Sheet 2:" & lastCol

End Sub

Output:

The last row in Sheet1: 1
The last column in Sheet1: 1
The last row in Sheet2: 1
The last column in Sheet2: 1

Get the Last Row/Column of a Worksheet Using a Function

The function below will return the last row or the last column based on the Worksheet argument.

Function GetLastRow(s As Worksheet) As Long
GetLastRow = s.Cells(s.Rows.Count, 1).End(xlUp).Row
End Function
Function GetLastCol(s As Worksheet) As Long
GetLastCol = s.Cells(1, s.Columns.Count).End(xlToLeft).Column
End Function

Sub testRow()
Dim wb As Workbook
Dim s1 As Worksheet

Set wb = ThisWorkbook
Set s1 = wb.Sheets("Sheet1")
Debug.Print GetLastRow(s1)
End Sub

Sub testCol()
Dim wb As Workbook
Dim s1 As Worksheet

Set wb = ThisWorkbook
Set s1 = wb.Sheets("Sheet1")
Debug.Print GetLastCol(s1)
End Sub

testRow Output:

1

testCol Output:

1