Excel custom function to find last column in the specified row. Function can return last column number, last column content and last column address.
Function can return last column number, last column content and last column address.
Function Name | LastColumnInRow(row_no, sheet_name, Optional dtype) |
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 |
'-----------------------------------------------------------------------------------------------------------------------------------------
'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
Function Usage
![]() |
Example usage of excel function to get Last Column in a row |
To return Last Column Number
=LastColumnInRow(1, "Sheet1", 0)
To return Last Column Value
=LastColumnInRow(1, "Sheet1", 1)
To return Last Column Address
=LastColumnInRow(1, "Sheet1", 2)
COMMENTS