VBA to copy values down and removed excess rows

y3tter

Board Regular
Joined
Nov 11, 2012
Messages
140
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
30,247
Office Version
365
Platform
Windows
How about
Code:
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
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
30,247
Office Version
365
Platform
Windows
You're welcome & thanks for the feedback
 

Forum statistics

Threads
1,077,827
Messages
5,336,613
Members
399,092
Latest member
jbwatkins

Some videos you may like

This Week's Hot Topics

Top