More

    Excel macro to find the last row in a column

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

    Introduction

    While writing macros, we face situations where we need to loop through each row in an excel sheet.

    To do that, we need to find the last row in an excel sheet which is not blank.

    The macro function we have can return the last row number, last row content value and address of the last row. This macro finds the last row of the column specified.

    Macro Function Details

    Macro Function NameLastRowInColumn(col_no, sheet_name, Optional dtype = 0)
    Macro Function DescriptionExcel macro function to find Last Row in Column
    Data Parameterscol_no:- Specify the column number
    sheet_name:- Specify the name of the sheet
    dtype:- 0 to return row number, 1 to return last row content, 2 to return last row address

    The Macro Function

    '------------------------------------------------------------------------------------------------------------------------------
    'Function Name                    : LastRowInColumn(col_no, sheet_name, Optional dtype = 0)
    'Function Description             : Excel Function to find Last Row in Column
    'Data Parameters                  : col_no:- Specify the column number
    '                                   sheet_name:- Specify the name of the sheet
    '                                   dtype:- 0 to return row number, 1 to return last row content, 2 to return last row address
    '------------------------------------------------------------------------------------------------------------------------------
    Public Function LastRowInColumn(col_no, sheet_name, Optional dtype)
        With Worksheets(sheet_name)
            LastRow = .Cells(.Rows.Count, col_no).End(xlUp).Row
        End With
        Select Case dtype
        Case 0
            LastRowInColumn = LastRow
        Case 1
            LastRowInColumn = Range(Split(Cells(, col_no).Address, "$")(1) & LastRow).Value
        Case 2
            LastRowInColumn = Replace(Range(Split(Cells(, col_no).Address, "$")(1) & LastRow).Address, "$", "")
        Case Else
            LastRowInColumn = LastRow
        End Select
    End Function

    Macro Function Usage

    To return Last Row Number
    =LastRowInColumn(2, "Sheet1", 0)

    To return Last Row Value
    =LastRowInColumn(2, "Sheet1", 1)

    To return Last Row Address
    =LastRowInColumn(2, "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