Excel macro to find the last column in a row
. 2 mins read
A custom Microsoft Excel macro function to find the last column in a row.
Introduction
While writing macros, we face situations where we need to loop through each column in an excel sheet.
To do that, we need to find the last column in an excel sheet which is not blank.
The macro function we have can return the last column number, last column content, and last column address. This macro finds the last column in the specified row.
The Function
Overview
- Name
- LastColumnInRow
- Description
- Excel macro function to find Last Column in a Row.
Parameters
- row_no
- Specify the row number whose last column needs to be identified.
- sheet_name
- Specify the sheet name.
- dtype
Specify the return type.
0to return column number.1to return column content.2to return column address.
The Macro Function
'-----------------------------------------------------------------------------------------------------------------------------------------
'Function Name : LastColumnInRow(col_no, sheet_name, Optional dtype = 0)
'Function Description : Excel Function to find Last Column in a Row
'Data Parameters : row_no:- Specify the row number
' sheet_name:- Specify the name of the sheet
' dtype:- 0 to return column number, 1 to return last column content, 2 to return last column address
'-----------------------------------------------------------------------------------------------------------------------------------------
Public Function LastColumnInRow(row_no, sheet_name, Optional dtype)
With Worksheets(sheet_name)
LastCol = .Cells(row_no, .Columns.Count).End(xlToLeft).Column
End With
Select Case dtype
Case 0
LastColumnInRow = LastCol
Case 1
LastColumnInRow = Range(Split(Cells(, LastCol).Address, "$")(1) & row_no).Value
Case 2
LastColumnInRow = Replace(Range(Split(Cells(, LastCol).Address, "$")(1) & row_no).Address, "$", "")
Case Else
LastColumnInRow = LastCol
End Select
End Function
Usage
To return last row number:
=LastColumnInRow(1, "Sheet1", 0)
To return last row value:
=LastColumnInRow(1, "Sheet1", 1)
To return last row address:
=LastColumnInRow(1, "Sheet1", 2)
- Excel
Category: