Selecting multiple rows relative to active cell

Question

I'm trying to create a VBA macro to

select two rows above the activecell
then autofill the two selected rows down one.

ActiveCell.EntireRow.Offset(-2, 0).Select selects the entire row (2 rows above the active cell), but I want it to select the row below this one as well. A recorded macro:

Range("A143").Activate
Selection.AutoFill Destination:=Rows("142:144"), Type:=xlFillDefault

Instead of absolute values for rows (rows 142:144), I want it to take the two selected rows above instead.

Do you want to auto fill the two rows above with the selected cell's row contets? — Nathan_Sav

Nathan_Sav Nathan_Sav · Accepted Answer · 2019-10-17T16:24:03

This may be of help

Dim r As Excel.Range
Dim r2 As Excel.Range

Set r = ActiveCell.EntireRow
Set r2 = ActiveCell.Offset(-2, 0).EntireRow.Resize(2)

r2.Value = r.Value   **** OR **** r.Copy r2

Selecting multiple rows relative to active cell

1 Answers