Find Last Row and Column
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
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 |
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
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 |
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 |
Ratings: 
(No Ratings Yet)
Loading...