VBA to copy values down and removed excess rows

[y3tter]

When I export my payroll to include employee's pay for the entire year, the software includes each pay period but only lists the employees name once. What I would like to accomplish is to copy their social, EID, Name and locations down to each non blank rows. Also, I need to remove the blank rows, rows that include subtotals and rows that are software page breaks.
I've included a download link to a small sample sheet.


Ex. Subtotal row - row 122
Ex. Software page break - rows 171, 172, 173, 174, 176 and 177


My actual spreadsheet is over 70,000 rows, so I'm assuming the best way to accomplish this would be using VBA. If anyone could help me out, I'd greatly appreciate it.


https://www.dropbox.com/s/ww2ff415tmhbmsp/Sample output.xlsx?dl=0

 

[Fluff]

How about

Sub y3tter()
   Dim Ar As Areas
   Dim Rng As Range
   
   With Range("G2", Range("G" & Rows.Count).End(xlUp))
      .Replace "Page", True, xlWhole, , False, , False, False
      Set Ar = .SpecialCells(xlConstants, xlLogical).Areas
   End With
   For Each Rng In Ar
      Rng.Resize(7).EntireRow.ClearContents
   Next Rng
   Range("E:E").SpecialCells(xlBlanks).EntireRow.Delete
   With Range("A3", Range("E" & Rows.Count).End(xlUp))
      .SpecialCells(xlBlanks).FormulaR1C1 = "=r[-1]c"
      .Value = .Value
   End With
End Sub

 

[y3tter]

Awesome, worked great!!

 

[Fluff]

You're welcome & thanks for the feedback