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

Question

I want to paste a value into a specific cell in Column A (an empty column) and have it automatically copy down to the next empty row or to the data in Column B.

I can do this by giving a range to copy down to, but since the number of rows copied can vary, I would want this to be dynamic. There are blocks of data separated by blank rows. In essence, I'd like to copy and paste a value for each block of information in column A.

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

Answer 1

Row 1048576 will be returned by end(xldown) since Column A is empty. You must check Column B for the conclusion before returning to Column A.

You can take Column B from your Selection using offset, then go back to Column A.

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