Home

Sorts for Excel macros don't hold

Deleted
Not applicable

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.

 

I don't know Visual Basic. I have previously easily created and run such simple macros. But today macros aren't working for me.

4 Replies
What steps did you take while recording the macro and what was the resulting code?

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

 

 

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

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

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
17 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
flashing a white screen while open new tab
cntvertex in Discussions on
11 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies