Thread: Loop through a range pasting in Blank Cells

I have a macro that will copy and paste to the next blank cell, but I need it to loop through the entire worksheet, with the condition that it skips the header rows in gray. In the attached sample worksheet I am tryting to copy E8:H8 to all blank cells to bottom, skipping row 21 & 24....etc. My macro copies and pastes correctly to Blank but i haven't figure out to make it loop and skip header properly.

Sub CopyH8ToBlanks()

   Range("E8:H8").Activate
   Selection.Copy

   lMaxRows = Cells(Rows.Count, "E").End(xlUp).Row
   Range("E" & lMaxRows + 1).Select
   Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
      False, Transpose:=False

End Sub

Hi

Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code. Posting code without them makes your code hard to read and difficult to be copied for testing. Highlight your code and click the # at the top of your post window. For more information about these and other tags, found here

Please note the above. As you are relatively new here I'll edit your post for you on this occasion.

Rather than looping through cells which is always more time consuming, try filtering data first. I don't know any quicker method for changing blocks of data. So something like

Sub CopyH8ToBlanks()
    Dim lLastRow As Long
    lLastRow = Range("A" & Rows.Count).End(xlUp).Row
    Range("C6").AutoFilter Field:=3, Criteria1:="<>"
    Range("E8:H8").Copy Destination:=Range("E8:E" & lLastRow)
    Cells.AutoFilter
End Sub