CODEWITHSUNDEEP
excelvba
Manny
Manny

Reputation: 103

Autofill Copy down up until next empty row- Excel VBA Macro

i would like to paste a value in a given cell in Column A (empty column), i would like to autofill copy this down up until the next empty row or data in column B.

I can do this by specifying a range to copy down to but i would like for this to be dynamic since the amount of rows copied can be different. There are blocks of data, with a blank row in between. I would like to essentially copy and paste a value for each block of data in column A. Thank you!

Selection.AutoFill Destination:=Range(Selection, Selection.End(xlDown)), Type:=xlFillCopy

Upvotes: 0

Views: 651

Answers (1)

Warcupine
Warcupine

Reputation: 4640

Since Column A is empty end(xldown) will return row 1048576. You need to look at Column B for the end then refer back to Column A.

You can use offset to grab Column B from your Selection then back to Column A.

Selection.AutoFill Destination:=Range(Selection, Selection.Offset(0, 1).End(xlDown).Offset(0, -1)), Type:=xlFillCopy

Upvotes: 1

Related Questions