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,571
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,571
Office Version
365
Platform
Windows
You're welcome & thanks for the feedback
 

Forum statistics

Threads
1,078,538
Messages
5,341,054
Members
399,414
Latest member
EMW2159

Some videos you may like

This Week's Hot Topics

  • Problem with Radio Button's format control
    I am creating an employee evaluation template (a sample is below) Column A is the category Column B, C D, E and F will be ratings (unacceptable...
  • Last Display on userform to a Listbox
    [CODE=vba] lstdisplay.ColumnCount = 15 lstdisplay.RowSource = "A1:O600000" [/CODE] So when i do this it Displays everything on the sheet i am...
  • Rename and move files to a new location
    Dear all, I have an excel file with the following information. The actual file name is at column A but i want to rename it using the following...
  • Help with True/False Formula
    Hello! Am stumped how to fix this formula, in which my result returns 'True', but it should return False. =IF(AG2=True...
  • Clear extra characters from a provided range of cells
    Dear All, I have following code which gives me desired output to remove extra characters from a provided range. But it takes too much time when...
  • Help with Current and highest streaks
    Hi there, I've just joined the forum and this is my first post. I've already spent quite a bit of time searching the net and this forum for a...
Top