VBA To Find Last Row or Column in a Excel Wroksheet.

Finding last row or column in a worksheet using VBA

Do you want to find out the last nonempty cell in both vertical (row) and horizontal (column) direction.

You can implement the following vba code in your script to detect the last row/column in a Excel worksheet. There are other ways as well to find this in a Worksheet, which uses Usedrange or Currentregion properties.

But we have used this function in most of our projects & it just works fine. So, lets get the code.

Finding Last Row in Excel Worksheet

The below code returns the last row number that is non empty. It takes the parameter as a range. So, You can either input the full sheet as range or a spcific column as well.

The sample function has 2 example illustrating the same.

'Function to test the code
Sub testGetLastRow()
    'Get last row in whole worksheet
    Debug.Print getLastRow(Sheets(1).Cells)
    
    'Get last row in a specific column
    Debug.Print getLastRow(Sheets(1).Columns(1))
End Sub

'Function that returns the Last row in sheet or range
Function getLastRow(iRng As Range) As Double
    'Define
    Dim fRng  As Range, iLastRow As Double, iLastCol As Double
    
    'Find for last row that is non empty
    Set fRng = iRng.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious)
    If Not (fRng Is Nothing) Then iLastRow = fRng.Row
    
    'Return Last row
    getLastRow = iLastRow
End Function

Once you run the first function, You can see the output in the immediate window using the Ctrl + G keys.

If you want to ind out the last column in addition to the last row, then use the function in the next section.

VBA Code – Find Last Row & Column

Copy the below code to a modules & you can call this function from your code. The function returns both last row & column in a variant array.

Function findlastRowLastColumn(shName As String) As Variant
    Dim vRet(1 To 2) As Double
    Dim iLastRow As Integer
    Dim iLastCol As Integer
    Dim iSh As Worksheet
    Dim fRng As Range
    
    'Init
    Set iSh = Sheets(shName)
    Set fRng = iSh.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious)
    If Not (fRng Is Nothing) Then iLastRow = fRng.Row
    Set fRng = iSh.Cells.Find("*", SearchOrder:=xlByColumns, SearchDirection:=xlPrevious)
    If Not (fRng Is Nothing) Then iLastCol = fRng.Column
    
    'Return
    vRet(1) = iLastRow
    vRet(2) = iLastCol
    findlastRowLastColumn = vRet
End Function

Code Explanation

If you would like to know more about how to do this refer to the below link as well.

External Link: Way to find last row or column in a worksheet