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

Rackette

New Member
Joined
Jul 2, 2019
Messages
37
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:

[TABLE="class: grid, width: 20, align: center"]
<tbody>[TR]
[TD]OUT[/TD]
[TD]IN[/TD]
[TD]Name[/TD]
[TD]Start Time[/TD]
[TD]End Time[/TD]
[TD]Location[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]Ashley[/TD]
[TD]0600[/TD]
[TD]0645[/TD]
[TD]a[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]Christine[/TD]
[TD]0600[/TD]
[TD]0645[/TD]
[TD]a[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]Christopher[/TD]
[TD]0615[/TD]
[TD]0700[/TD]
[TD]a[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]Lanny[/TD]
[TD]0615[/TD]
[TD]0700[/TD]
[TD]a[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]Mark[/TD]
[TD]0615[/TD]
[TD]0700[/TD]
[TD]a[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]Mary[/TD]
[TD]0645[/TD]
[TD]0730[/TD]
[TD]a[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]Neil[/TD]
[TD]0700[/TD]
[TD]0745[/TD]
[TD]a[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]Pat[/TD]
[TD]0700[/TD]
[TD]0745[/TD]
[TD]a[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]Paul[/TD]
[TD]0700[/TD]
[TD]0745[/TD]
[TD]a[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]Rennie[/TD]
[TD]0715[/TD]
[TD]0800[/TD]
[TD]b[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]Rob[/TD]
[TD]0730[/TD]
[TD]0815[/TD]
[TD]a[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]Robert[/TD]
[TD]0730[/TD]
[TD]0815[/TD]
[TD]a[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]Roger[/TD]
[TD]0730[/TD]
[TD]0815[/TD]
[TD]a[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
I need the macro to change it to the following pattern.

[TABLE="class: grid, width: 20, align: left"]
<tbody>[TR]
[TD]Out[/TD]
[TD]In[/TD]
[TD]Name[/TD]
[TD]Start Time[/TD]
[TD]End Time[/TD]
[TD]Location[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]Ashley[/TD]
[TD]0600[/TD]
[TD]0645[/TD]
[TD]a[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]Christine[/TD]
[TD]0600[/TD]
[TD]0645[/TD]
[TD]a[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Out[/TD]
[TD]In[/TD]
[TD]Name[/TD]
[TD]Start Time[/TD]
[TD]End Time[/TD]
[TD]Location[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]Christopher[/TD]
[TD]0615[/TD]
[TD]0700[/TD]
[TD]a[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]Lanny[/TD]
[TD]0615[/TD]
[TD]0700[/TD]
[TD]a[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]Mark[/TD]
[TD]0615[/TD]
[TD]0700[/TD]
[TD]a[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Out[/TD]
[TD]In[/TD]
[TD]Name[/TD]
[TD]Start Time[/TD]
[TD]End Time[/TD]
[TD]Location[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]Mary[/TD]
[TD]0645[/TD]
[TD]0730[/TD]
[TD]a[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Out[/TD]
[TD]In[/TD]
[TD]Name[/TD]
[TD]Start Time[/TD]
[TD]End Time[/TD]
[TD]Location[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]Neil[/TD]
[TD]0700[/TD]
[TD]0745[/TD]
[TD]a[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]Pat[/TD]
[TD]0700[/TD]
[TD]0745[/TD]
[TD]a[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]Paul[/TD]
[TD]0700[/TD]
[TD]0745[/TD]
[TD]a[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
 

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".
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
 
Upvote 0
Fluff, I can't thank you enough!
It works perfectly.
A huge thanks to you and everyone else who stops in here to help! :)
 
Upvote 0
You're welcome & thanks for the feedback
 
Upvote 0
I have the same need with the exception as my header row in on row 7 instead of row 1. I have company header information in rows 1-6. When I tweet the code above, I get blank lines and the formatting of the header rows. What am I doing wrong.
 
Upvote 0
What did you change the code to?
 
Upvote 0
Here is what the data looks like before I run my tweaked macro:
1631280506834.png

Here is the code after I tweaked it. I changed the number of blank lines from 5 to 3 and changed the copied header row from 1 to 7 and changed it to look at column 6 for when the data changes.

1631280656752.png

This is what I get after I run the Macro:

1631280803729.png

And this is what I want it to look like after the Macro:

1631281026560.png
 

Attachments

  • 1631280276398.png
    1631280276398.png
    121.7 KB · Views: 3
  • 1631280604687.png
    1631280604687.png
    9.7 KB · Views: 3
Upvote 0
Change the loop to stop at row 8 not row 3
 
Upvote 0
Thanks that worked pretty well but it still inserts blank lines at the top. See below. I guess I could just add to delete rows 7-10 after the marco rums and that would solve the problem of the extra rows.

1631283563530.png
 
Upvote 0
Oops, I should have said change it to 9 not 8
 
Upvote 0

Forum statistics

Threads
1,224,820
Messages
6,181,162
Members
453,021
Latest member
Justyna P

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top