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

[Rackette]

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]

 

[Fluff]

How about

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

 

[Rackette]

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

 

[Fluff]

You're welcome & thanks for the feedback

 

[SBNUT]

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.

 

[Fluff]

What did you change the code to?

 

[SBNUT]

Here is what the data looks like before I run my tweaked macro:

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.

This is what I get after I run the Macro:

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

 

[Fluff]

Change the loop to stop at row 8 not row 3

 

[SBNUT]

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.

 

[Fluff]

Oops, I should have said change it to 9 not 8