AutoFilter.Sort.SortFields.Clear无法清除排序字段问题求助

问题背景 #

通过VBA代码利用透视表行区域列对另一工作表数据排序汇总，透视表变更后点击命令按钮重算。但执行AutoFilter.Sort.SortFields.Clear后，仍存在旧排序字段（累计至13个），最终导致XLSM文件损坏。手动排序箭头会消失，但数据仍按旧列组合排序。

已尝试的清除写法 #

• Worksheets(pSheet).AutoFilter.Sort.SortFields.Clear
• Activesheet.AutoFilter.Sort.SortFields.Clear

With Activesheet
    AutoFilter.Sort.SortFields.Clear
End With

原始代码 #

Function SortTab(pSheet As String, pCol As String) As Boolean
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' Sort entire tab by 1 to 5 columns
'
'Code examples from recording a Macro when sorting
'    ActiveWorkbook.Worksheets("Data").AutoFilter.Sort.SortFields.Add Key:=Range("I2:I1019"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
'    ActiveWorkbook.Worksheets("Data").AutoFilter.Sort.SortFields.Add Key:=Range("J2:J1019"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
'
'Parameters:
' pSheet    Sheet to find range     Example: Data
' pCol      Last column on sheet    Example: BC100
'
'Global Variables:
' gRange        UsedRange for current sheet Example: A1:BC100
' gPivotCols    Array of column names to Sort   Example: gPivotCols(1) = 5 for "Dept", gPivotCols(2) =  4 for "Company"
' gColSort  Last column in the sort     Example: 4 for the Company column being the 4th column in the data sheet
'gColMax        Max # of Sort Columns is 5
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
On Error GoTo Err_SortTab
    
    Dim i           As Integer
       
    'Remove all Fiters & Sorts
    Worksheets(pSheet).Activate                             'Select sheet
    Worksheets(pSheet).AutoFilterMode = False               'Turn AutoFilter off
    Worksheets(pSheet).UsedRange.AutoFilter                 'Turn AutoFilter on
    Worksheets(pSheet).AutoFilter.Sort.SortFields.Clear     'Clear any Sorts
    
'Code inserted to find out how many sort columns exist after a AutoFilter.Sort.SortFields.Clear   
'Currently returning a value of 13 sorts  
i = ExistingSorts()
If i > 0 Then
    MsgBox i & " existing sorts still exist", vbCritical, "Error"
    SortTab = False
    Exit Function
End If
    
    With ActiveSheet.Sort
        .Header = xlYes                 'Tab has Headers
        .MatchCase = False              'Not case sensitive
        .Orientation = xlTopToBottom    'Sort Top to Bottom
        .SetRange Range(gRange)         'Complete range on Data tab to sort example: A1:BC100
        
        For i = 1 To gColMax            'Loop throuh column heading in the Pivot table
            If gPivotCols(i) = 0 Then   'Check for less than 5 columns
                Exit For
            End If
            gColSort = gPivotCols(i)    'Set the last column in the Sort
            
           .SortFields.Add Key:=Range(gRange).Columns(gColSort), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal       'Add sort column
            
        Next i
        .Apply
    End With


Exit_SortTab:
    SortTab = True
    Exit Function
    
Err_SortTab:
    If Application.UserName = "Murf" Then
        MsgBox Err.Description, vbOKOnly, "Error #: " & Err.Number
        Stop
        Resume
    End If

End Function


Function ExistingSorts() As Integer
'''''''''''''''''''''''''''''''''''''''''''''''''''
' Copied from internet
' Count existing sorts
'''''''''''''''''''''''''''''''''''''''''''''''''''
On Error Resume Next

    Dim S   As Sort
    Dim SF  As SortField

    Set S = ActiveSheet.Sort
    
    ExistingSorts = S.SortFields.Count

End Function

问题根源 #

AutoFilter.Sort与Worksheet.Sort是两个独立的对象：前者是筛选状态下的排序配置，后者是工作表自身的排序配置。代码中仅清空了AutoFilter.Sort的字段，而后续使用的ActiveSheet.Sort对象的旧排序字段始终未被清除，导致字段持续积累。

修正方案 #

直接操作工作表自身的Sort对象，彻底清空旧字段，同时避免依赖Activate/ActiveSheet，改用指定工作表对象提升稳定性。

修正后代码 #

Function SortTab(pSheet As String, pCol As String) As Boolean
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' 按1到5列排序整个工作表
'
'宏录制的排序代码示例
'    ActiveWorkbook.Worksheets("Data").AutoFilter.Sort.SortFields.Add Key:=Range("I2:I1019"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
'    ActiveWorkbook.Worksheets("Data").AutoFilter.Sort.SortFields.Add Key:=Range("J2:J1019"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
'
'参数:
' pSheet    目标工作表名称     示例: Data
' pCol      工作表最后一列    示例: BC100
'
'全局变量:
' gRange        当前工作表已用区域 示例: A1:BC100
' gPivotCols    用于排序的列名数组 示例: gPivotCols(1) = 5 对应"部门", gPivotCols(2) = 4 对应"公司"
' gColSort      排序的最后一列     示例: 4 表示公司列是数据工作表的第4列
' gColMax       最大排序列数为5
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
On Error GoTo Err_SortTab
    
    Dim i           As Integer
    Dim targetSheet As Worksheet
    Set targetSheet = Worksheets(pSheet) '直接绑定目标工作表对象
       
    '移除所有筛选和排序
    targetSheet.AutoFilterMode = False               '关闭自动筛选
    targetSheet.UsedRange.AutoFilter                 '重新打开自动筛选
    
    '关键：清空工作表自身的Sort字段，而非筛选的Sort字段
    targetSheet.Sort.SortFields.Clear
    
    '检查剩余排序字段
    i = ExistingSorts(targetSheet) '传入目标工作表，确保统计准确
    If i > 0 Then
        MsgBox i & " 个排序字段仍未清除", vbCritical, "错误"
        SortTab = False
        Exit Function
    End If
    
    With targetSheet.Sort '使用绑定的工作表对象，避免依赖ActiveSheet
        .Header = xlYes                 '表头存在
        .MatchCase = False              '不区分大小写
        .Orientation = xlTopToBottom    '从上到下排序
        .SetRange targetSheet.Range(gRange) '限定排序范围为目标工作表区域
        
        For i = 1 To gColMax            '遍历透视表列标题
            If gPivotCols(i) = 0 Then   '不足5列时退出循环
                Exit For
            End If
            gColSort = gPivotCols(i)    '设置当前排序列
            
           .SortFields.Add Key:=.SetRange.Columns(gColSort), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal       '添加排序列
            
        Next i
        .Apply
    End With

Exit_SortTab:
    SortTab = True
    Exit Function
    
Err_SortTab:
    If Application.UserName = "Murf" Then
        MsgBox Err.Description, vbOKOnly, "错误编号: " & Err.Number
        Stop
        Resume
    End If

End Function


Function ExistingSorts(ws As Worksheet) As Integer
'''''''''''''''''''''''''''''''''''''''''''''''''''
' 统计指定工作表的现有排序字段数量
'''''''''''''''''''''''''''''''''''''''''''''''''''
On Error Resume Next

    Dim S   As Sort
    Dim SF  As SortField

    Set S = ws.Sort '使用传入的工作表对象
    
    ExistingSorts = S.SortFields.Count

End Function

核心修改点 #

1. 绑定targetSheet对象，替代Activate和ActiveSheet，避免上下文切换错误
2. 将排序字段清除操作改为targetSheet.Sort.SortFields.Clear，直接清空工作表自身的排序配置
3. 修改ExistingSorts函数接收工作表参数，确保统计目标工作表的排序字段数量
4. 排序范围和字段添加时，均使用targetSheet对象限定，避免范围歧义

内容的提问来源于stack exchange，提问作者Murf