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 the last row in a Worksheet, which uses Usedrange or Currentregion properties.

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

VBA Code – Find Last Row & Column

Copy the below code to a modules and you can call this function from your code. The function returns both last row and column in a 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

Line 3 – 6 are for declaring the variables:
• iLastRow; this will contain the last nonempty row number
• iLastCol; this will contain the last nonempty column number
• iSh; used to specify the worksheet you want evaluated
• fRng; a mnemonic for doing the evaluation

You need to replace the name of your worksheet at line 8.

Line 10 and 11 will look through all the rows, starting from row 1048579 going backwards, to find the first nonempty row.

Line 13 and 14 will look through all the columns, starting from column XFD (count 16384) going backwards, to find the first nonempty column.

Line 16 and 17 will produce dialog box with the last row and column number.

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