Auto duplicate grouping

Question

I have basic knowledge of VB and above intermediate in excel. But I am stuck where I have a list of Product Codes, How Many Available, and then the Location. These locations are sorted Ascending and will have duplicates. What I would like to do is to group each set of locations together and shade every second group (by rows) a different color so they are easier to read when printed or to add a row after each separate group. This would be easier if I didn't have to filter out certain ones using Auto Filter so it would be useful to have it apply to whatever is filtered through, rather then the whole array. An example of the Location column could go as such: XA-01-01-1 XA-01-01-1 XA-01-01-1 XA-01-01-3 XB-01-04-1 XB-01-04-1 XB-01-10-3 XD-01-04-4 XD-01-04-4 XD-01-04-4 I would like it to automatically created a space after each set of locations, like after XA-01-01-01, or to have the different shading. I would like to do this using a macro.

Answer

assume the locations are in column C and they are already sorted by location. I understand you will have some locations filtered out and you want to work on what is visible.

Sub AABB() Dim lastrow As Long, loc As Long Dim i As Long ' find the last visible row lastrow = Cells(Rows.Count, 3).End(xlUp).Row ' now loop from the bottom up loc = Cells(lastrow, 3).Row For i = lastrow To 2 Step -1

 If Rows(i).Hidden = False And Cells(i, 3) <> Cells(loc, 3) Then
      Rows(i + 1).EntireRow.Insert
      Rows(i + 1).EntireRow.Hidden = False
      loc = i
 End If

Next

End Sub

That worked for me with your example data with some rows inserted and filtered out using autofilter. It inserts the blank rows. Test it on a copy of your data until you are satisfied it does what you want. If it doesn't, then contact me via email with a sample file telling me what the macro does that is not acceptable or at least what you want.