AndyTampa
Board Regular
- Joined
- Aug 14, 2011
- Messages
- 184
- Office Version
- 365
- 2016
- Platform
- Windows
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:
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.
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.