Formula to copy row A down?

sobeitjedi

Board Regular
Joined
Mar 13, 2006
Messages
235
Office Version
  1. 365
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

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).
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
 
Upvote 0
Sorry, I mean in VBA? The quantity of rows will differ so I can't commit to a specific row number
 
Upvote 0
Try

Code:
Sub MM1()
Range("A1:A" & Cells(Rows.Count, "C").End(xlUp).Row).Formula = "=IF($C1="""","""",A$1)"
End Sub
 
Upvote 0
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.
 
Upvote 0
Try this:

Michael M adjusted code.
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
 
Upvote 0
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.
 
Upvote 0
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)"
 
Upvote 0

Forum statistics

Threads
1,212,927
Messages
6,110,695
Members
448,293
Latest member
jin kazuya

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top