Excel macro to find the last column in a row

Aneejian · Nov 25, 2012 · 2 mins read.

Excel macro to find the last column in a row

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


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


Excel macro function to find Last Column in a Row.


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

Specify the return type.

0 to return column number.

1 to return column content.

2 to 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


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)