Macro Help Please

[Guest]

Hello Group
I have a file that has data in cells A1:F1, A5:F5, A9:F9 etc. I would like
to copy the data in the first row down and fill in the blanks where A2:F4
=A1:F1 A6:F8 = A5:F5, A10:F14=A9:F9 etc
How do I do this?
Thanks!

 

[Guest]

If this is a one-time need, I'd just create the formulas to do it...
Select your entire table (A1:F2000 or whatever). Turn on autofilters (Data

Filter > Autofilter) and select 'blanks' in the first dropdown. Since it's

blank, row 2 should be visible. In A2 enter the formula =A1. Autofill that
formula across to column F and then down to all the visible rows. Turn off
your filter and all the blank rows should be there.
If you need to 'lock down' the values (getting rid of the formulas we just
entered), select the entire table and Copy / Paste Special values.

 

[Guest]

Thanks for the response. Not a one time deal. I am trying to add to
existing code that is run in a regular basis. Thanks anyway!

 

[Guest]

Ok, then VBA it is...
Dim sourcerow
For sourcerow = 1 To 9 Step 4 'fill in the appropriate start and end row#s
Range(Cells(sourcerow, 1), Cells(sourcerow, 6)).Copy
Range(Cells(sourcerow + 1, 1), Cells(sourcerow + 3, 6)).PasteSpecial _
Paste:=xlPasteValues
Next

 

[Guest]

OK But row 9 is dynamic how do I incorporate last row?
Thanks

 

[Guest]

Whatever your variables are, you can just change the conditions on the For.
Alternately, you could create something like
Dim sourcerow
sourcerow = 1
While Cells(sourcerow, 1) <> ""
Range(Cells(sourcerow, 1), Cells(sourcerow, 6)).Copy
Range(Cells(sourcerow + 1, 1), Cells(sourcerow + 3, 6)).PasteSpecial
Paste:=xlPasteValues
sourcerow = sourcerow + 4
Wend
This just continues until it finds column A blank in the next sourcerow.