Separate list of people by inserting blank rows

[AndyTampa]

Hey there. This may be simple, but I haven't been able to find a solution that I can edit properly. I work with data that comes in lists, but it's not sorted. I've been able to sort the list with a macro using 5 columns, but I want to further add spacing between sets of similar data.

I have the following VBA code to sort the list:

Sub PAFbypeople()

    If Not ActiveSheet.AutoFilterMode Then
        ActiveSheet.Range("A1").AutoFilter
    End If
With ActiveSheet.Sort
     .SortFields.Add Key:=Range("AD1"), Order:=xlDescending
     .SortFields.Add Key:=Range("AA1"), Order:=xlDescending
     .SortFields.Add Key:=Range("AC1"), Order:=xlAscending
     .SortFields.Add Key:=Range("I1"), Order:=xlAscending
     .SortFields.Add Key:=Range("J1"), Order:=xlAscending
     .SetRange Range("A1:AJ501")
     .Header = xlYes
     .Apply
End With
End Sub

Column AD is the product type
Column AA identifies the account owner
Column AC is each person's ID
Column I is the begin date
Column J is the end date.

I know that turning the filters on is not a requirement for the sort actions. I just want it turned on.

What I'd like to do now is add 2 blank rows between each set of people with the same number in column AC (their ID numbers).

As a bonus, I'd like to gray shade the entire row if Column I = Column J (begins and ends the same day).

I think the shading would probably best be done before adding the blank rows while all the data is a single table, but you guys would know best.

 

[Osvaldo Palmeiro]

Hi. Try this, please.

Sub InsertRows()
 Dim LR As Long, k As Long
  LR = Cells(Rows.Count, 1).End(3).Row - 1
  Application.ScreenUpdating = False
  For k = LR To 2 Step -1
   If Cells(k, 9) = Cells(k, 10) Then Cells(k, 1).Resize(, 30).Interior.ColorIndex = 15
   If Cells(k, 29) <> Cells(k + 1, 29) Then Rows(k + 1).Resize(2).Insert: Rows(k + 1).Resize(2).Interior.ColorIndex = xlNone
  Next k
End Sub

 

[AndyTampa]

Hi. Try this, please.

Sub InsertRows()
Dim LR As Long, k As Long
  LR = Cells(Rows.Count, 1).End(3).Row - 1
  Application.ScreenUpdating = False
  For k = LR To 2 Step -1
   If Cells(k, 9) = Cells(k, 10) Then Cells(k, 1).Resize(, 30).Interior.ColorIndex = 15
   If Cells(k, 29) <> Cells(k + 1, 29) Then Rows(k + 1).Resize(2).Insert: Rows(k + 1).Resize(2).Interior.ColorIndex = xlNone
  Next k
End Sub

That comes close. However, I had to figure out that it was using column 1 which is blank in my data. Once I selected column 2 it worked except for one problem. The bottom row had the same dates and they didn't get highlighted. I managed to figure out that I had to remove the -1 from the LR variable but I don't understand why you resize the rows or even how that part of the macro works. It works to insert 2 rows, but now that I've removed the -1, I think it's inserting 2 rows at the end of the data, not that it's a problem. I can't see how it's inserting those rows.

I've got it working, but I can't help but think that something I'm not thinking about will be affected by the resizing. Can you explain the parts of the macro for me that resize and how it adds two rows, please? I'm trying to learn this stuff.

 

[Osvaldo Palmeiro]

Yes, you're right, the code isn't looking for I=J on the last row, sorry for that. I've changed that code line to ~~~> LR = Cells(Rows.Count, 2).End(3).Row and this line will look for the last row in column 2 instead of column 1, and it seems will work fine. The diference is that the code will always insert two blank rows below the last row because AC & LR will hold data, but it will always be different from AC & LR +1, which will always be blank, so the code will insert two blank lines below data, but this doesn't matter, as you said.

 

[AndyTampa]

I'm still wrapping my head around how .Resize works and what it does. I thought it resized the rows height. Does it just resize the selection?

Also, what color number would I use to just make the gray a little darker?

 

[Osvaldo Palmeiro]

At a first glance, "Resize ()" can pass the impression that it is about resizing the height of a row or the width of a column, but what it does is to extend the range by a certain amount of rows and/or columns from a given range reference.
Range.Resize property (Excel)
VBA: How do I use negative range for the .Resize function?

For a gray a little darker, paint a cell of the desired color, keep it selected, run the code below. It will put the color number in the cell to the right of the selected one.

Sub ColorNumber()
 ActiveCell.Offset(, 1).Value = ActiveCell.Interior.ColorIndex
End Sub

 

[AndyTampa]

Showoff ?

 

[AndyTampa]

I've hit a little snag in the macro and not sure how to fix it. It works perfectly to separate by people. I never considered how it would work for a single person with more than one selection under column 30. The sorting still works, but the macro that checks each line against the one above it is only checking peoples ID numbers. The sorting prevents the issue by adding other ID numbers into the mix.

For this scenario, I'd like the macro to also check if column 30 changes when the member number is the same. This seems like it would be a nested IF statement, but I don't fully understand the macro as it is. I've never seen a nested IF macro.

In other words, if the two member numbers match, but the products are different, insert the lines anyway. Would this be like "if the member numbers AND the products match, do nothing, otherwise add the lines"?

 

[Osvaldo Palmeiro]

... if the two member numbers match (column 29), but the products are different (column 30), insert the lines anyway.

Please, try the modified code below.

Sub InsertRows()
 Dim LR As Long, k As Long
  LR = Cells(Rows.Count, 2).End(3).Row
  Application.ScreenUpdating = False
  For k = LR To 2 Step -1
   If Cells(k, 9) = Cells(k, 10) Then Cells(k, 1).Resize(, 30).Interior.ColorIndex = 15
   If Cells(k, 29) = Cells(k + 1, 29) And Cells(k, 30) <> Cells(k + 1, 30) Then
    Rows(k + 1).Resize(2).Insert: Rows(k + 1).Resize(2).Interior.ColorIndex = xlNone
   End If
  Next k
End Sub

 

[AndyTampa]

Please, try the modified code below.

Sub InsertRows()
 Dim LR As Long, k As Long
  LR = Cells(Rows.Count, 2).End(3).Row
  Application.ScreenUpdating = False
  For k = LR To 2 Step -1
   If Cells(k, 9) = Cells(k, 10) Then Cells(k, 1).Resize(, 30).Interior.ColorIndex = 15
   If Cells(k, 29) = Cells(k + 1, 29) And Cells(k, 30) <> Cells(k + 1, 30) Then
    Rows(k + 1).Resize(2).Insert: Rows(k + 1).Resize(2).Interior.ColorIndex = xlNone
   End If
  Next k
End Sub

It didn't work. Not only didn't it insert blank lines between products, but it didn't insert them between people either. Let me see if I can explain better.

My macro first sorts by product and then by name. So the original code would work up the list and see the name change between product 1 and product 2 as long as there was more than one person in the list, then insert the lines required. The problem I'm having now is when it comes up from the bottom and there is only one person, it doesn't see a name change and doesn't insert the rows even though the product has changed.

I think there shouldn't be an AND in that line. I tried with OR, but it failed miserably that way. It just added a bunch of lines inappropriately and grouped different people together, and that was with only a single product. It looks like you changed the "<>" in the second IF statement to "=". I changed it back and included the OR and my first test seems to have worked, but that's hardly a good sample.

 If Cells(k, 29) <> Cells(k + 1, 29) Or Cells(k, 30) <> Cells(k + 1, 30) Then

Do you see anything wrong with what I've done?