使用VBA在excel中用数据对每个单元格进行排序

Question

我录制了一个用于排序此工作表的宏。如何替换硬编码的范围，以便该脚本可以使用任意数量的行？

Worksheets(1).sort.SortFields. _
Clear
Worksheets(1).sort.SortFields. _
Add2 Key:=Range("A2:A130008"), SortOn:=xlSortOnValues, Order:=xlAscending _
, DataOption:=xlSortNormal
Worksheets(1).sort.SortFields. _
Add2 Key:=Range("B2:B130008"), SortOn:=xlSortOnValues, Order:=xlAscending _
, DataOption:=xlSortNormal
Worksheets(1).sort.SortFields. _
Add2 Key:=Range("C2:C130008"), SortOn:=xlSortOnValues, Order:=xlAscending _
, DataOption:=xlSortNormal
Worksheets(1).sort.SortFields. _
Add2 Key:=Range("D2:D130008"), SortOn:=xlSortOnValues, Order:=xlAscending _
, DataOption:=xlSortNormal
Worksheets(1).sort.SortFields. _
Add2 Key:=Range("E2:E130008"), SortOn:=xlSortOnValues, Order:=xlAscending _
, DataOption:=xlSortNormal
Worksheets(1).sort.SortFields. _
Add2 Key:=Range("P2:P130008"), SortOn:=xlSortOnValues, Order:= _
xlDescending, DataOption:=xlSortNormal
Worksheets(1).sort.SortFields. _
Add2 Key:=Range("Q2:Q130008"), SortOn:=xlSortOnValues, Order:= _
xlDescending, DataOption:=xlSortNormal
With Worksheets(1).sort
.SetRange Range("A1:R130008")
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With

Answer 1

这应该完成任务。请试试。

Sub StackOverflow()
Dim Rng         As Range            ' range to sort

With Worksheets(1)
Set Rng = .Range(.Cells(2, "A"), .Cells(.Rows.Count, "A").End(xlUp)) _
.Resize(, .Columns("R").Column)
' .Columns("R").Column = 18 and may be replaced with this number

With .Sort.SortFields
.Clear
' specify the columns to sort on in sequence of priority
.Add2 Key:=Rng.Cells(1), SortOn:=xlSortOnValues, _
Order:=xlAscending, DataOption:=xlSortNormal
.Add2 Key:=Rng.Cells(2), SortOn:=xlSortOnValues, _
Order:=xlAscending, DataOption:=xlSortNormal
.Add2 Key:=Rng.Cells(3), SortOn:=xlSortOnValues, _
Order:=xlAscending, DataOption:=xlSortNormal
.Add2 Key:=Rng.Cells(4), SortOn:=xlSortOnValues, _
Order:=xlAscending, DataOption:=xlSortNormal
.Add2 Key:=Rng.Cells(5), SortOn:=xlSortOnValues, _
Order:=xlAscending, DataOption:=xlSortNormal
.Add2 Key:=Rng.Cells(16), SortOn:=xlSortOnValues, _
Order:=xlAscending, DataOption:=xlSortNormal
.Add2 Key:=Rng.Cells(17), SortOn:=xlSortOnValues, _
Order:=xlAscending, DataOption:=xlSortNormal
End With
With .Sort
.SetRange Rng
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
End With
End Sub

请注意，在.Sort.SetRange中，必须指定整个范围，而对于设置每个键，只需要一个单元格来标识列。由于一个范围内的单元格编号为1，先上后下，该范围内的前18个单元格编号与下表范围内的列编号一致。因此，根据Rng的指定方式，Key:=Rng.Cells(1)恰好是Worksheets(1).Cells(2, "A")。我选择了较短的语法，但效果是在应用其他键之前，整个范围将首先在列A上排序。