Inserting rows with headers

[PipBoy808]

I imagine this is something pretty straightforward that I just have no experience with. In the attached workbook I want the 'Original' sheet to look like the 'Desired' sheet. I'm not really worried about column widths (although if code can be written to make these wide enough that'd be great). My main concern is inserting rows with the day headers. The amount of distinct days in the data will vary, so I need the code to be dynamic. I guess I could verbalise it as follows:

"Hey Excel, I need you to note how many distinct days there are, then every time the day in Column E changes, I need you to put in a new row with a header in column A equal to the new day."

I've accomplished the first part [thanks to Luke M yesterday!] and have a variable called "Days" that is equal to the distinct amount of days in the collection, but I don't know where to go from here. Any ideas? Thanks in advance. I'm coming along in leaps and bounds thanks to this forum. THE POWER!

 

[Luke M]

Try this out:

Sub TEST()
Dim lastRow As Long
Dim myDay As String, newDay As String
myDay = ""
Application.ScreenUpdating = False
With Worksheets("Original")
    lastRow = .Cells(.Rows.Count, "E").End(xlUp).Row
 
 
    For Each c In .Range("E2", .Cells(lastRow, "E"))
        newDay = c.Value
        If newDay <> myDay Then
            'New day detected!
            c.EntireRow.Insert
            .Cells(c.Row - 1, "A").Value = newDay
            myDay = newDay
        End If
    Next c
End With
Application.ScreenUpdating = True
End Sub

 

[PipBoy808]

Well if that didn't work like an absolute charm! Thanks a lot. As usual however, I want to be absolutely sure I understand every detail so my version has a ton of comments explaining every line.

By the way, you forgot to do this:

Dim c as Variant

but I won't hold it against you Thanks!

 

[Luke M]

By the way, you forgot to do this:

Dim c as Variant

but I won't hold it against you Thanks!

Ha ha, you got me. Although, you should make it

Dim c as Range

rather than just a Variant. My fault for being in a rush.