Add header row in excel when string changes

Question

I have an excel worksheet that has a column "A" with a string path in it like this:

foo\bar
foo\bar
foo\bar
foo\bar
foo\bar
foo\widget
foo\widget
foo\widget
foo\zelda
foo\zelda
foo\zelda

I need to add a header row before each change in the path so it should look like this:

Bar:
foo\bar
foo\bar
foo\bar
foo\bar
foo\bar
Widget:
foo\widget
foo\widget
foo\widget
Zelda:
foo\zelda
foo\zelda
foo\zelda

I am not sure where to even start with this as I am not a vba expert. Is this something that is possible in VBA?

BruceWayne · Accepted Answer · 2017-03-20 21:40:02Z

2

This should work (assuming your data is in Column A):

Sub insertHeaderRow()
Application.ScreenUpdating = False

Dim lastRow    As Long, i As Long
Dim cel        As Range
Dim myTest As String

lastRow = Cells(Rows.Count, 1).End(xlUp).Row
For i = lastRow To 1 Step -1
    Set cel = Cells(i, 1)
    mytext = Mid(cel, InStrRev(cel, "\") + 1, 256) & ":"
    On Error Resume Next
    If cel.Value <> cel.Offset(-1, 0).Value Or cel.Row = 1 Then
        cel.EntireRow.Insert
        cel.Offset(-1, 0).Value = mytext
        colorHeaderRow cel.Offset(-1, 0)
        ' Double header row height
        cel.Offset(-1, 0).RowHeight = cel.Offset(-1, 0).RowHeight * 2
    End If
    On Error GoTo 0
Next i

Application.ScreenUpdating = True

End Sub

Private Sub colorHeaderRow(ByVal cel As Range)
With cel.EntireRow.Interior
    .Pattern = xlSolid
    .PatternColorIndex = xlAutomatic
    .ThemeColor = xlThemeColorLight1
    .TintAndShade = 0
    .PatternTintAndShade = 0
End With
With cel.EntireRow.Font
    .ThemeColor = xlThemeColorDark1
    .TintAndShade = 0
End With
End Sub

Note: If someone has any idea how to avoid using the On Error Resume Next (without making the code much longer), I'd appreciate it. I only used it because at row 1, the cel.offset(-1,0).Value throws an (expected) error and doesn't look at the rest of the statement. I used the Resume Next so it'll ignore that, and see the cel.Row = 1 and add the final row. I've just had it drilled in to my head to avoid error handling like this...but the code shouldn't throw any other errors.

edited Mar 20, 2017 at 21:40

answered Mar 20, 2017 at 20:57

BruceWayne

23.5k16 gold badges75 silver badges126 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

VinnyGuitara Over a year ago

This looks really promising but I am getting "Unable to get the Search property of the worksheet function class" Is this something due to my data?

BruceWayne Over a year ago

@VinnyGuitara - It should work. I just ran it. ...but then got the same error after a few times of testing it. Let me re-check this.

BruceWayne Over a year ago

@VinnyGuitara - I updated the code, totally removing the use of Search which I also thinks makes it a little easier to understand. Let me know if it throws any more errors or issues.

VinnyGuitara Over a year ago

This is perfect, is there a chance you can show me how to add in to make the header rows black, with white font and double the row height? This would just be for the header rows. Although this does work perfectly well.

BruceWayne Over a year ago

@VinnyGuitara - Yeah, no problemo! Check my edit. I added a second Sub to keep it easier to follow (IMO).

Collectives™ on Stack Overflow

Add header row in excel when string changes

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related