Insert a row above unique value in column using VBA

[Lensas]

Hello experts,

I have a report that I need to format quite often, to make it more readable I need to insert yellow rows above every market. Any suggestions on how to find the first unique value in a column?

To make it more clear: Column Q contains country names and by adding yellow lines I want to distinguish sections for every separate country.

Hope this makes sense. Thanks in advance.

 

[mse330]

Welcome to Mr Excel

Try the below VBA code … To add the code, you need to follow the below steps:

1. Press Alt+F11
2. Press Alt+I+M
3. Paste the below VBA code below
4. Close the VBA editor & select the sheet you need to add the yellow rows
5. Press F8 & run the code
6. Ensure to save the file in macro enable format for the code to work (.xlsm or .xlsb)

Sub Add_Yellow_Rows()
With ActiveSheet
    For x = .Range("Q" & Rows.Count).End(xlUp).Row To 2 Step -1
        If .Range("Q" & x).Value <> .Range("Q" & x).Offset(-1).Value Then
            With Rows(x)
                .EntireRow.Insert
                .Offset(-1).Interior.Color = vbYellow
            End With
        End If
    Next
End With
End Sub

 

[Lensas]

Thank you mse!

I will try it out and come back if any issues, looks quite simple, but somehow did not manage to crack it myself...

 

[Lensas]

Thanks once again, I studied the code you provided and adjusted it to my needs. Works perfectly, and I finally learned the Step keyword!

 

[mse330]

Glad to help & thanks for reporting back