Insert the header row proceeded by 4 blank rows at each change in a column value
Results 1 to 4 of 4

Thread: Insert the header row proceeded by 4 blank rows at each change in a column value
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Jul 2019
    Posts
    26
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Insert the header row proceeded by 4 blank rows at each change in a column value

    Good morning
    I've been looking all over for a macro that does what I need, but I have been unable to find one, so I will appreciate ANY help someone can offer.

    I have a report that has about 150 rows and 9 columns. One of the columns is "Start Time". Every time there is a change in "Start Time", I need to insert 4 blank rows PLUS a 5th row that is an exact copy of the header row (font size, font, fill color....everything needs to be the same as row 1). The total number of rows is dynamic and the number of rows in each "Start Time" grouping is dynamic. The first two columns will always be blank except where the header row has been inserted. The first two columns are for manually entering a check mark when the person goes out and then another when they are back in.

    This is what I'd start with:

    OUT IN Name Start Time End Time Location
    Ashley 0600 0645 a
    Christine 0600 0645 a
    Christopher 0615 0700 a
    Lanny 0615 0700 a
    Mark 0615 0700 a
    Mary 0645 0730 a
    Neil 0700 0745 a
    Pat 0700 0745 a
    Paul 0700 0745 a
    Rennie 0715 0800 b
    Rob 0730 0815 a
    Robert 0730 0815 a
    Roger 0730 0815 a
    I need the macro to change it to the following pattern.

    Out In Name Start Time End Time Location
    Ashley 0600 0645 a
    Christine 0600 0645 a
    Out In Name Start Time End Time Location
    Christopher 0615 0700 a
    Lanny 0615 0700 a
    Mark 0615 0700 a
    Out In Name Start Time End Time Location
    Mary 0645 0730 a
    Out In Name Start Time End Time Location
    Neil 0700 0745 a
    Pat 0700 0745 a
    Paul 0700 0745 a

  2. #2
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    26,897
    Post Thanks / Like
    Mentioned
    459 Post(s)
    Tagged
    45 Thread(s)

    Default Re: Insert the header row proceeded by 4 blank rows at each change in a column value

    How about
    Code:
    Sub Rackette()
       Dim i As Long
       
       For i = Range("D" & Rows.Count).End(xlUp).Row To 3 Step -1
          If Cells(i, 4) <> Cells(i - 1, 4) Then
             Rows(i).Resize(5).Insert
             Rows(1).Copy Rows(i + 4)
          End If
       Next i
    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
    New Member
    Join Date
    Jul 2019
    Posts
    26
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Insert the header row proceeded by 4 blank rows at each change in a column value

    Fluff, I can't thank you enough!
    It works perfectly.
    A huge thanks to you and everyone else who stops in here to help!

  4. #4
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    26,897
    Post Thanks / Like
    Mentioned
    459 Post(s)
    Tagged
    45 Thread(s)

    Default Re: Insert the header row proceeded by 4 blank rows at each change in a column value

    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
  •