Forum Discussion

Deleted

Feb 06, 2019

Sorts for Excel macros don't hold

Hello, when I record a macro in Excel that has a sequence of sorts, the sorts don't hold. For example, I sort column A, then sort column B. The column A sort is lost as soon as I sort column B. ...

excel

Macros and VBA

Deleted

Feb 06, 2019

Jan, thanks for your help. Below is the code of a short test that:

1. Filters on two values of Column BR

2. Sorts ascending Column BR

3. Sorts ascending Column BV.

As soon as I sort Column BV as I record the macros, I lose the sort in Column BR. No blank rows or blank columns in the spreadsheet

Sub PrincipalGiftTiers1_2()
'
' PrincipalGiftTiers1_2 Macro
' segmentation protocol: filter tiers 1:2. Sort Tiers, Donor Segment, WealthPoint Rating, Loyal Donor, Last Gift Date, Cumulative
'

'
ActiveSheet.Range("$A$1:$CA$578").AutoFilter Field:=70, Criteria1:= _
"=Tier 1: $500K+", Operator:=xlOr, Criteria2:="=Tier 2: $250K-$500K"
ActiveWorkbook.Worksheets("Rhode Island PG Tiers and Loyal").AutoFilter.Sort. _
SortFields.Clear
ActiveWorkbook.Worksheets("Rhode Island PG Tiers and Loyal").AutoFilter.Sort. _
SortFields.Add2 Key:=Range("BR1:BR578"), SortOn:=xlSortOnValues, Order:= _
xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Rhode Island PG Tiers and Loyal").AutoFilter. _
Sort
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
ActiveWorkbook.Worksheets("Rhode Island PG Tiers and Loyal").AutoFilter.Sort. _
SortFields.Clear
ActiveWorkbook.Worksheets("Rhode Island PG Tiers and Loyal").AutoFilter.Sort. _
SortFields.Add2 Key:=Range("BV1:BV578"), SortOn:=xlSortOnValues, Order:= _
xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Rhode Island PG Tiers and Loyal").AutoFilter. _
Sort
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
End Sub

JKPieterse

Silver Contributor

Feb 07, 2019

I think you should have recorded the sort by clicking (Home tab) Sort & FIlter, Custom sort and then setting up the two sorts in the custom sort dialog. That would have resulted in code like below:

Sub PrincipalGiftTiers1_2()
'
' PrincipalGiftTiers1_2 Macro
' segmentation protocol: filter tiers 1:2. Sort Tiers, Donor Segment, WealthPoint Rating, Loyal Donor, Last Gift Date, Cumulative
'
'
    ActiveSheet.Range("$A$1:$CA$578").AutoFilter Field:=70, Criteria1:= _
                                                 "=Tier 1: $500K+", Operator:=xlOr, Criteria2:="=Tier 2: $250K-$500K"
    ActiveWorkbook.Worksheets("Rhode Island PG Tiers and Loyal").AutoFilter.Sort. _
            SortFields.Clear
    ActiveWorkbook.Worksheets("Rhode Island PG Tiers and Loyal").AutoFilter.Sort. _
            SortFields.Add2 Key:=Range("BR1:BR578"), SortOn:=xlSortOnValues, Order:= _
                            xlAscending, DataOption:=xlSortNormal
    ActiveWorkbook.Worksheets("Rhode Island PG Tiers and Loyal").AutoFilter.Sort. _
            SortFields.Add2 Key:=Range("BV1:BV578"), SortOn:=xlSortOnValues, Order:= _
                            xlAscending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("Rhode Island PG Tiers and Loyal").AutoFilter. _
         Sort
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
End Sub

Deleted
Feb 07, 2019
Jan,

Yes! Thank you! For some reason, I had it in my head that recording the macro allowed me to skip having to do the custom sort. Regards, Randy