Forum Discussion

Deleted's avatar
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.

 

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

excel
Macros and VBA

4 Replies

  • JKPieterse's avatar
    JKPieterse
    Silver Contributor
    Feb 06, 2019
    What steps did you take while recording the macro and what was the resulting code?
    • Deleted's avatar
      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's avatar
        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