Copy/Paste to all Blank rows

[Lovelylou79]

Hello,

I need to paste my header row into every blank row on my sheet. The header is at Row 2. Each blank row is spaced 5 rows apart.

I've tried several variations of his code, to no success;

ActiveSheet.UsedRange.Select
Dim i As Long
With Application
.Calculation = xlCalculationManual
.ScreenUpdating = False
For i = Selection.Rows.Count To 1 Step -1
If WorksheetFunction.CountA(Selection.Rows(i)) = 0 Then
Rows("2").Copy
ActiveCell.PasteSpecial xlPasteAll

End If
Next i
.Calculation = xlCalculationAutomatic
.ScreenUpdating = True
End With

Where am I going wrong?

 

[My Aswer Is This]

So the first blank row is 7 and the next blank row is 12
Is that correct?

You first said blank row then you said every fifth row.

 

[Lovelylou79]

Yes, the first blank row is 7, then blank at 12, 17 and so on until the last row.
I think I'd prefer the code to recognise that these are blank row rather than rows 7,12,17 etc... incase there is ever an instance when the blank row is not on 7, 12, 17 etc.
Else if row 7, 12 etc...were not blank then exit the sub I guess.

 

[My Aswer Is This]

So where do I look to see if the row is blank?
We have 15,000 columns where data might be on each row.

 

[Lovelylou79]

The code above looks in the UsedRange. Is that not correct? It works for deleting entire rows, I thought it could be amended to paste rows, I just cant figure out how.

 

[My Aswer Is This]

Try this:

Sub Fill_Rows()
Application.ScreenUpdating = False
Dim ans As Long
ans = ActiveSheet.UsedRange.Rows.Count
    For i = 2 To ans
        If WorksheetFunction.CountA(Rows(i)) = 0 Then Rows(2).Copy Rows(i)
    Next
Application.ScreenUpdating = True
End Sub

 

[Lovelylou79]

Thank you for the code, it won't run citing 'Next without For' as an error.
I have used my code with your Then Statement and it works a treat.

I was so close, I obviously need to understand the use of i in these codes better.

 

[My Aswer Is This]

Well my code had all the end if statements it needed so not sure why your saying it did not work.
I test all my scripts.

 

[MARK858]

Maybe if all the blank cells in column A are truly blank and match the rows you want filled then the below will do...

Sub xxxx()
    Dim lc As Long
    lc = Cells(2, Columns.Count).End(xlToLeft).Column
    On Error Resume Next
    Range(Cells(2, 1), Cells(2, lc)).Copy _
            Range("A2:A" & Range("A" & Rows.Count).End(xlUp).Row).SpecialCells(4)
    On Error GoTo 0
End Sub

BTW, the code M.A.I.T. posted works fine for me if the rows are truly blank (I did declare the variable i though).

 

[Lovelylou79]

My apologies, I tried MAIT's code again, and it does work beautifully. I must have done something wrong the first few times.
I feel like your head butting icon is directed at numpty's like me.