Formula to copy row A down?

[sobeitjedi]

Hi

I need a formula that will copy the data is the first row of AFGIJKLMNOPQR down the sheet, as far down as long as there is data in column C?

Can you help?

 

[Special-K99]

in A1
=IF($C1="","",A1)

Is it ALWAYS copied from row 1 ?
=IF($C1="","",A$1)

Copy the formula to the other columns F to R
and down the sheet for those columns

 

[sobeitjedi]

Sorry, I mean in VBA? The quantity of rows will differ so I can't commit to a specific row number

 

[Michael M]

Try

Sub MM1()
Range("A1:A" & Cells(Rows.Count, "C").End(xlUp).Row).Formula = "=IF($C1="""","""",A$1)"
End Sub

 

[sobeitjedi]

No - didn't work

A1 contains a plus sign, but the formula has put 0 for all rows down. The data in FGIJKLMNOPQR hasn't copied down.

 

[Tom.Jones]

Try this:

Michael M adjusted code.

Sub MM1()
Range("A2:A" & Cells(Rows.Count, "C").End(xlUp).Row).Formula = "=IF($C1="""","""",A$1)"
Range("F2:G" & Cells(Rows.Count, "C").End(xlUp).Row).Formula = "=IF($C1="""","""",F$1)"
Range("I2:R" & Cells(Rows.Count, "C").End(xlUp).Row).Formula = "=IF($C1="""","""",I$1)"
End Sub

 

[sobeitjedi]

Looks great - thank you!

Can I add one more thing into the mix? .... if the first row is blank in any of AFGIJKLMNOPQR, then don't fill down, for that column only.

 

[Michael M]

Try

If Range("A1") <> "" Then Range("A2:A" & Cells(Rows.Count, "C").End(xlUp).Row).Formula = "=IF($C1="""","""",A$1)"
If Range("F1") <> "" Then Range("F2:G" & Cells(Rows.Count, "C").End(xlUp).Row).Formula = "=IF($C1="""","""",F$1)"
If Range("I1") <> "" Then Range("I2:R" & Cells(Rows.Count, "C").End(xlUp).Row).Formula = "=IF($C1="""","""",I$1)"