Collection

May 20, 2019
No Comments

Last Row and Column

Ratings: 1 Star2 Stars3 Stars4 Stars5 Stars (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
Tags: ,