VBA to copy value in active cell down to end of data

[Trebor200]

I would like to take value in A12 and copy down to end of data set... The active cell can be anything other than A12 and the data set can also be many rows long..

Book1
	A	B
5	Colour	Date
6	Blue	17.11.2022
7	Blue	17.11.2023
8	Blue	17.11.2024
9	Pink	17.11.2025
10	Pink	17.11.2026
11	Pink	17.11.2027
12	Brown	17.11.2028
13		17.11.2029
14		17.11.2030
15		17.11.2031
16		17.11.2032
17		17.11.2033
18		17.11.2034
19		17.11.2035
20		17.11.2036
Sheet1

 

[Fluff]

How about

Sub Trebor()
   With Range("A2:A" & Range("B" & Rows.Count).End(xlUp).Row)
      .SpecialCells(xlBlanks).FormulaR1C1 = "=r[-1]c"
      .Value = .Value
   End With
End Sub

 

[Joe4]

How about

Sub Trebor()
   With Range("A2:A" & Range("B" & Rows.Count).End(xlUp).Row)
      .SpecialCells(xlBlanks).FormulaR1C1 = "=r[-1]c"
      .Value = .Value
   End With
End Sub

Fluff,

When I tested your code on Trebor's data, it populted the blank cells, but it also populated the rows above the table (rows 2-4).
I think you may need to alter your code a little, i.e.

   With Range("A5:A" & Range("B" & Rows.Count).End(xlUp).Row)

 

[Trebor200]

Perfect as always!! Thanks

 

[Trebor200]

Fluff,

When I tested your code on Trebor's data, it populted the blank cells, but it also populated the rows above the table (rows 2-4).
I think you may need to alter your code a little, i.e.

   With Range("A5:A" & Range("B" & Rows.Count).End(xlUp).Row)

Yes... i figured that out and changed and works perfect.

 

[Fluff]

When I tested your code on Trebor's data, it populted the blank cells, but it also populated the rows above the table (rows 2-4).

Oops, didn't notice the mini sheet started on row 5.

Perfect as always!! Thanks

Glad we could help & thanks for the feedback.