Forum Discussion

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 s...

excel

Macros and VBA

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