Forum Discussion

Harsh_Khurana's avatar
Harsh_Khurana
Copper Contributor
Nov 17, 2022

Macros for automation

I am new to Macros. I want to record certain operations and use them in different sheets having same format of columns (variables) but different data size (row). When I record the operations in one sheet having say 50 rows and then use that macro in a sheet having 100 rows, the operation happens only in 50 rows, which is logical and understandable. But how do I overcome this? I may have sheets with different row sizes in which I want to use this macro and I want the macro to operate till the last row. Is it possible? I have zero concept of VBA coding. I just recorded some steps and tried to use the macro in other sheets having same format.

excel
Macros and VBA

1 Reply

  • HansVogelaar's avatar
    HansVogelaar
    MVP
    Nov 17, 2022

    Harsh_Khurana 

    It depends on what you want to do.

    In some situations, you can refer to ActiveSheet.UsedRange instead of to a specific range.

    In others, you have to find the last used row, for example:

        Dim LastRow As Long
    Dim MyRange As Range
    ' Find the last used row in column A
    LastRow = Range("A" & Rows.Count).End(xlUp).Row
    ' Specify the range in columns A to M, from row 2 to the last used row
    Set MyRange = Range("A2:M" & LastRow)

    See this recent discussion for some examples: To run a macro on another excel sheet with a different number of rows 

Resources