More

    Excel macro to find the last column in a row

    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.

    Macro Function Details

    Macro Function NameLastColumnInRow(row_no, sheet_name, Optional dtype)
    Macro Function DescriptionExcel macro function to find Last Column in a Row
    Data Parametersrow_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

    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

    Function Usage

    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)

    Related Articles

    Recent Articles

    How to fix Date Time data type issue in Blue Prism?

    Know more about the date time issue while using the code stage in Blue Prism and understand how to fix it by dealing with the UTC offset.

    How to deserialize JSON in UiPath?

    Tutorial on how to properly deserialize JSON with UiPath Web Activities. Explains the concept of JSON Array and JSON Object.

    Dynamic or Variable Selectors in UiPath

    Tutorial on how to use dynamic selectors in UiPath. Explains how to use variables in UiPath selectors.

    Change Case Excel Add-In

    Change Case Excel Add-In is a powerful Excel Add-In that can change the case of selected cells. With easy to use Keyboard shortcuts, perform case conversion in style.

    Blue Prism Video Tutorial

    A well-structured video tutorial on Blue Prism, which is the right starting point for your RPA career. #rpa #blueprism

    Leave a Reply

    This site uses Akismet to reduce spam. Learn how your comment data is processed.

    Stay on top - Get latest articles in your inbox