Excel Function to find Last Column in a Row

Excel custom function to find last column in the specified row. Function can return last column number, last column content and last column address.

Excel custom function to find last column in the specified row. Function can return last column number, last column content and last column address

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 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)