vba - 循环宏可单独处理所有列，无需编写长长的重复转换列表

Question

我正在尝试循环此宏(如下)以遍历所有数据列，但需要一些帮助来创建循环或调整当前代码以适用于所有列。

Sub Trial_5()

'
    ActiveCell.Offset(0, -7).Columns("A:A").EntireColumn.Select
    ActiveWorkbook.Worksheets("Sheet6").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Sheet6").Sort.SortFields.Add Key:=ActiveCell, _
        SortOn:=xlSortOnValues, Order:=xlDescending,    DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("Sheet6").Sort
        .SetRange ActiveCell.Range("A1:A16395")
        .Header = xlGuess
        .MatchCase = False
        .Orientation = xlTopToBottom
        `enter code here`.SortMethod = xlPinYin
       .Apply
    End With
End Sub

我是否将 .columns 或 activecell.offset 调整为总范围？

调整脚本

Sub eachcolumndesending()
'
' eachcolumndesending Macro
' descending
'

'
    Columns("A:A").Select
    ActiveWorkbook.Worksheets("Sheet5").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Sheet5").Sort.SortFields.Add Key:=Range("A1"), _
        SortOn:=xlSortOnValues, Order:=xlDescending,    DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("Sheet5").Sort
        .SetRange Range("A2:A32")
        .Header = xlNo
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    Columns("B:B").Select
    ActiveWorkbook.Worksheets("Sheet5").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Sheet5").Sort.SortFields.Add Key:=Range("B1"), _
        SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("Sheet5").Sort
        .SetRange Range("B1:B33")
        .Header = xlNo
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With

Answer 1

我建议您从记录的代码转向 VBA 单列排序实际需要的代码。

Sub sortAllColumns()
    Dim c As Long

    On Error Resume Next  '<~~ may be necessary if a column breaks the sort 
    With Worksheets("Sheet5")
        For c = .UsedRange.Columns.Count To 1 Step -1
            With .Columns(c)
                .Cells.Sort Key1:=.Columns(1), Order1:=xlDescending, _
                            Orientation:=xlTopToBottom, Header:=xlGuess
            End With
        Next c
    End With
End Sub

顺便说一句，您可能不必猜测 header 是否存在。要么存在(xlYes)，要么不存在(xlNo)，但你比我更了解你的数据。