Results 1 to 4 of 4

Thread: VBA to copy values down and removed excess rows

  1. #1
    Board Regular
    Join Date
    Nov 2012
    Posts
    140
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default VBA to copy values down and removed excess rows

    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/ww2ff415tm...tput.xlsx?dl=0

  2. #2
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    28,128
    Post Thanks / Like
    Mentioned
    470 Post(s)
    Tagged
    47 Thread(s)

    Default Re: VBA to copy values down and removed excess rows

    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
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  3. #3
    Board Regular
    Join Date
    Nov 2012
    Posts
    140
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA to copy values down and removed excess rows

    Awesome, worked great!!

  4. #4
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    28,128
    Post Thanks / Like
    Mentioned
    470 Post(s)
    Tagged
    47 Thread(s)

    Default Re: VBA to copy values down and removed excess rows

    You're welcome & thanks for the feedback
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •