Autofill across and above a dynamic range

Question

I'd like to autofill cells across and above a dynamic range.

I have a line of numbers in row 3 and would like to put the word "Customer No." in the cell above each one.

I do this by copying A2 and pasting into C2 then dragging across

Via VBA macro recorder the code I get looks like this

Selection.Copy
Range("C2").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Selection.AutoFill Destination:=Range("C2:E2"), Type:=xlFillDefault
Range("C2:E2").Select

I was wondering if there's a way to create an autofill across a dynamic range as the number of cells in row 3 will change from time to time?

Thanks

Thanks Sam, what functionality in Conditional Formatting could I use exactly? — Bikat Uprety, Apr 05 '22 at 14:29
Build the condition based on if the cell is empty. If true, make the text white. — Sam, Apr 05 '22 at 16:06

Tim Williams · Answer 1 · 2022-04-05T15:17:19.710

0

You could do it like this:

With ActiveSheet
    .Range("C3", .Cells(3, Columns.Count).End(xlToLeft)).Offset(-1).Value = .Range("A2").Value
End With

edited Apr 05 '22 at 15:17

answered Apr 04 '22 at 18:40

Tim Williams

154,628
8
97
125

Thanks for this, however, when I use this code the word "Customer No." goes into the cell A1 and it fills the first three cells across, regardless of how many cells there are in row 2. – Bikat Uprety Apr 05 '22 at 08:16
My mistake - typo `2` instead of `3` – Tim Williams Apr 05 '22 at 15:18

Autofill across and above a dynamic range

1 Answers1