Excel macro to find the last row in a column

Aneejian · Nov 24, 2012 · 2 mins read.

Excel macro to find the last row in a column

A custom Microsoft Excel macro function to find the last row of a specified column.


While writing macros, we face situations where we need to loop through each row in an excel sheet.

To do that, we need to find the last row in an excel sheet which is not blank.

The macro function we have can return the last row number, last row content value and address of the last row. This macro finds the last row of the column specified.

The Function


Excel macro function to find Last Row in Column


Specify the column number whose last row needs to be identified.
Specify the sheet name.

Specify the return type.

0 to return row number.

1 to return row content.

2 to return row address.

'Function Name                    : LastRowInColumn(col_no, sheet_name, Optional dtype = 0)
'Function Description             : Excel Function to find Last Row in Column
'Data Parameters                  : col_no:- Specify the column number
'                                   sheet_name:- Specify the name of the sheet
'                                   dtype:- 0 to return row number, 1 to return last row content, 2 to return last row address
Public Function LastRowInColumn(col_no, sheet_name, Optional dtype)
    With Worksheets(sheet_name)
        LastRow = .Cells(.Rows.Count, col_no).End(xlUp).Row
    End With
    Select Case dtype
    Case 0
        LastRowInColumn = LastRow
    Case 1
        LastRowInColumn = Range(Split(Cells(, col_no).Address, "$")(1) & LastRow).Value
    Case 2
        LastRowInColumn = Replace(Range(Split(Cells(, col_no).Address, "$")(1) & LastRow).Address, "$", "")
    Case Else
        LastRowInColumn = LastRow
    End Select
End Function


To return last row number: =LastRowInColumn(2, "Sheet1", 0)

To return last row value: =LastRowInColumn(2, "Sheet1", 1)

To return last row address: =LastRowInColumn(2, "Sheet1", 2)