Excel VBA 按特定列对命名范围进行排序
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/16335684/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Excel VBA Sorting a named range by a particular column
提问by baarkerlounger
I have a selected range and within that Range I would like to sort by a particular column so that each row is kept the same i.e. the sort function just reorders the entire rows based on whichever column I have selected to sort by.
我有一个选定的范围，在该范围内我想按特定列排序，以便每一行保持相同，即排序函数只是根据我选择排序的列重新排序整行。
My code so far is this:
到目前为止我的代码是这样的：
Sub CustomSortAscendingExcel(ByVal control As IRibbonControl, ByRef cancelDefault As Variant)
Dim selRange As range
Dim usedRange As range
Dim checkRange As range
Dim shtData As Worksheet
Set shtData = Worksheets("Data")
shtData.Activate
shtData.Unprotect
Set selRange = Selection
Set usedRange = returnUsedRange
Set checkRange = Intersect(usedRange, selRange)
If Not Application.Intersect(usedRange, selRange) Is Nothing Then
With ActiveSheet
usedRange.Sort Key1:=selRange, Order1:=xlAscending, DataOption1:=xlSortNormal, MatchCase:=False, SortMethod:=xlPinYin, _
Header:=xlNo, Orientation:=xlSortColumns
.Sort.Apply
End With
And similar for sorting Descending. This works fine for column1. My problem is that for columns 2, 3, 4 it only works correctly for those rows that have a value in column1 not for any that don't. For example in the image below sorting by column 3 correctly reorders the first 9 rows but not the last two.
与降序排序类似。这适用于 column1。我的问题是，对于第 2、3、4 列，它仅适用于在 column1 中有值的行，而不适用于没有值的行。例如，在下图中，按第 3 列排序正确地重新排序前 9 行而不是最后两行。
Can anyone suggest a way to fix this? Thanks!
任何人都可以提出解决此问题的方法吗？谢谢！
回答by
I recorded a simple macro that selects Range("A1:D5") , applies filter, then sorts in ascending order based on the 3rd column (C)
我录制了一个简单的宏，它选择 Range("A1:D5") ，应用过滤器，然后根据第 3 列 (C) 按升序排序
look at this:
看这个：
Range("A1:D5").Select
Selection.AutoFilter
ActiveWorkbook.Worksheets("Sheet1").AutoFilter.Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Sheet1").AutoFilter.Sort.SortFields.Add Key:=Range _
("C1:C5"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
With ActiveWorkbook.Worksheets("Sheet1").AutoFilter.Sort
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Now, there isnt much you have to do to fix your problem :)
现在，您无需做太多事情来解决您的问题:)
