# Formula to copy row A down?

#### sobeitjedi

##### Board Regular
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?

### Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.

#### Special-K99

##### Well-known Member
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

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

#### Michael M

##### Well-known Member
Try

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

#### sobeitjedi

##### Board Regular
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

##### Board Regular
Try this:

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

##### Board Regular
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

##### Well-known Member
Try

Code:
``````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)"``````

1,089,301
Messages
5,407,458
Members
403,143
Latest member
CTremblay