我在 Excel 2013 中创建了一个表格,但我只想对选定的单元格进行排序。表格范围是 A1:I200,但我只想对 A150:I200 进行排序。我尝试选择单元格,右键单击,排序,它对整个表格进行了排序。有解决方案吗?
答案1
以下宏可解决这个问题。首先取消列出表格,然后对选定的范围进行排序,然后再次创建 ListObject。您应该调整 Sub 以根据需要微调细节。
Sub part_sort()
Dim rng_sel As Range, rng_all As Range
Dim lo_table As ListObject, lo_name As String
Dim ws As Worksheet
Set ws = ActiveSheet
Set rng_sel = Selection
Set lo_table = rng_sel.ListObject
lo_name = lo_table.Name
Set rng_all = lo_table.Range
Debug.Print rng_all.Address
lo_table.Unlist
With ws.Sort
.SortFields.Clear
.SortFields.Add Key:=rng_sel.Columns(1), _
SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
.SetRange rng_sel
.Header = xlGuess
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Set lo_table = ws.ListObjects.Add(xlSrcRange, rng_all, , xlYes)
lo_table.Name = lo_name
End Sub
PS:这个问题可能更适合 Stack Overflow。