vba VBA中的自动过滤器排序
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/35695901/
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
Auto Filter sort in VBA
提问by estephan500
I'm making a VBA macro. At one point, the macro tells excel to change the current autofilter to have the autofilter's sort be on the column in Column A, and it should be ascending.
我正在制作一个 VBA 宏。在某一时刻，宏告诉 excel 更改当前的自动筛选器，以使自动筛选器的排序位于 中的列上Column A，并且它应该是升序的。
But, the VBA code that I have now states that the RANGE is hard-coded which is Range(A1:A655),that's because the code was written in a recorded Macro. Below is my VBA code. Can you tell me how to change the A1:A655not in a hard-coded way, instead make it just generically mean "make the range be ALL of the rows in that column, however much there is?
但是，我现在拥有的 VBA 代码声明 RANGE 是硬编码的，这是Range(A1:A655)因为代码是用录制的宏编写的。下面是我的 VBA 代码。你能告诉我如何以A1:A655硬编码的方式改变not ，而是让它只是泛指“使范围成为该列中的所有行，无论有多少？
ActiveWorkbook.Worksheets("A1").AutoFilter.Sort.SortFields.Add Key:=Range(
"A1:A655"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=
xlSortTextAsNumbers
回答by
Try to avoid relying on the ActiveSheet property. You should know what worksheet you are on and explicitly reference it by the Worksheet .CodeNameor Worksheet .Nameproperty.
尽量避免依赖ActiveSheet 属性。您应该知道您在哪个工作表上，并通过 Worksheet .CodeName或 Worksheet .Name属性显式引用它。
I believe you've mixed up a Worksheet Objectwith a Range object. That is, unless you actually have a worksheet named A1which does not seem likely.
我相信您已经将Worksheet Object与Range object混淆了。也就是说，除非您确实有一个名为A1的工作表，这似乎不太可能。
The Range.CurrentRegion propertyis an excellent method of referencing the uninterrupted block of cells radiating out from A1. It continues down and right until it meets a fully blank row and fully blank column. Single or small groups of intermediate blank cells do not interrupt its reference to the block of cells.
所述Range.CurrentRegion属性是引用细胞A1辐射出来的不间断的块的极好方法。它继续向下和向右，直到遇到完全空白的行和完全空白的列。单个或一小组中间空白单元格不会中断其对单元格块的引用。
With ActiveWorkbook
With .Worksheets("Sheet1")
If .AutoFilterMode Then .AutoFilterMode = False
With .Cells(1, 1).CurrentRegion
.Cells.Sort Key1:=.Columns(1), Order1:=xlAscending, _
Orientation:=xlTopToBottom, Header:=xlYes
With .Resize(.Rows.Count, 141) '<~~ A:EK
.AutoFilter Field:=36, Criteria1:=1
'the CurrentRegion is sorted on column A and filtered
'on column AJ
End With
End With
End With
End With
The nested With ... End With statements progessively defines the block of cells you wish to reference.
嵌套的With ... End With 语句逐步定义要引用的单元格块。
I believe you are better off with a conventional Range.Sort method. It is more straightforward than a .AutoFilter.Sort. A subsequent AutoFilter methodfinishes off the operation.
我相信您最好使用传统的Range.Sort 方法。它比 .AutoFilter.Sort 更直接。随后的AutoFilter 方法完成操作。
