Last Row and Column

Ratings:     (No Ratings Yet) Loading…

DESCRIPTION:
There are certain situations where we perform some tasks by finding last used Row with data in a Column. For examples, There may be many columns with data and each column may have different number of items (rows). In this situation we need to find exact number of rows in a specific column to avoid the unnecessary looping of all rows even if there is no data.

Find Last Row

Sub FindLastRow()
Dim i As Integer
i = FindLastRow1
End Sub

Function FindLastRow1() As Integer
Dim lRow As Long
Dim lCol As Long
Dim sht As Worksheet

Set sht = ThisWorkbook.Worksheets("Sheet1")

'Using Row A
lRow = sht.Cells(sht.Rows.Count, "A").End(xlUp).Row
Debug.Print "Using row A – Last row: " & lRow; ""

'Using Region – No blank on the rows
lRow = sht.Range("A1").CurrentRegion.Rows.Count
Debug.Print "Using region – Last Row: " & lRow; ""

'Using Table
lRow = sht.ListObjects("Table1").Range.Rows.Count
Debug.Print "Using Table – Last Row: " & lRow; ""

'Using Range
Dim Myrange As Range

Set Myrange = Range("A1:C7")
lRow = Myrange.Rows.Count
Debug.Print "Using range – Last Row: " & lRow; ""

End Function

Find Last Column

Sub FindLastCol()
Dim i As Long
i = FindLastColFunction
End Sub

Function FindLastColFunction() As Integer

Dim lRow As Long
Dim lCol As Long
Dim sht As Worksheet

Set sht = ThisWorkbook.Worksheets("Sheet1")

'Find Last Column
lCol = sht.Cells(sht.Columns.Count).End(xlToLeft).Column
Debug.Print "Last column: " & lCol

'Using region
lCol = sht.Range("A1").CurrentRegion.Columns.Count
Debug.Print "Using region – Last col: " & lCol

'Using Table
lCol = sht.ListObjects("Table1").Range.Columns.Count
Debug.Print "Using Table – Last col: " & lCol

'Using Range
Dim Myrange As Range
Set Myrange = Range("A1:C7")

lCol = Myrange.Columns.Count
Debug.Print "Using range – Last col: " & lCol

End Function