Separate list of people by inserting blank rows

AndyTampa

Board Regular
Joined
Aug 14, 2011
Messages
125
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:

VBA Code:
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.
 

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.

Osvaldo Palmeiro

Well-known Member
Joined
Feb 24, 2009
Messages
689
Office Version
  1. 365
Platform
  1. Windows
Hi. Try this, please.

VBA Code:
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
 
Solution

AndyTampa

Board Regular
Joined
Aug 14, 2011
Messages
125
Hi. Try this, please.

VBA Code:
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

Well-known Member
Joined
Feb 24, 2009
Messages
689
Office Version
  1. 365
Platform
  1. Windows
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

Board Regular
Joined
Aug 14, 2011
Messages
125

ADVERTISEMENT

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

Well-known Member
Joined
Feb 24, 2009
Messages
689
Office Version
  1. 365
Platform
  1. Windows
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.
VBA Code:
Sub ColorNumber()
 ActiveCell.Offset(, 1).Value = ActiveCell.Interior.ColorIndex
End Sub
 

AndyTampa

Board Regular
Joined
Aug 14, 2011
Messages
125

ADVERTISEMENT

Showoff 😉
 

AndyTampa

Board Regular
Joined
Aug 14, 2011
Messages
125
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

Well-known Member
Joined
Feb 24, 2009
Messages
689
Office Version
  1. 365
Platform
  1. Windows
... 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.
VBA Code:
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

Board Regular
Joined
Aug 14, 2011
Messages
125
Please, try the modified code below.
VBA Code:
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.

VBA Code:
 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?
 

Forum statistics

Threads
1,140,993
Messages
5,703,606
Members
421,305
Latest member
ambuj Thakur

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top