Excel macro to find the last row in a column
. 2 mins read
A custom Microsoft Excel macro function to find the last row of a specified column.
Introduction
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
Overview
- Name
- LastRowInColumn
- Description
- Excel macro function to find Last Row in Column
Parameters
- col_no
- Specify the column number whose last row needs to be identified.
- sheet_name
- Specify the sheet name.
- dtype
Specify the return type.
0to return row number.1to return row content.2to 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
Usage
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)
- Excel
Category: