VBA中的自动筛选排序 [英] Auto Filter sort in VBA

问题描述

我正在制作VBA宏.某一时刻，宏告诉excel更改当前的自动过滤器，以使自动过滤器的排序位于Column A的列中，并且应该递增.

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代码指出RANGE是Range(A1:A655)硬编码的，这是因为该代码是在记录的宏中编写的.下面是我的VBA代码.您能告诉我如何不以硬编码的方式更改A1:A655，而是使其仅表示使范围成为该列中的所有行，但有多少行呢?"

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:A655 not 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?

ActiveWorkbook.Worksheets("A1").AutoFilter.Sort.SortFields.Add Key:=Range(
        "A1:A655"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=
        xlSortTextAsNumbers

推荐答案

尝试避免依赖 ActiveSheet属性.您应该知道您正在使用的工作表，并由工作表明确引用.代号或 工作表 .Name 属性.

Try to avoid relying on the ActiveSheet property. You should know what worksheet you are on and explicitly reference it by the Worksheet .CodeName or Worksheet .Name property.

我相信您混淆了工作表对象与范围对象.也就是说，除非您实际上有一个不太可能的名为 A1 的工作表.

I believe you've mixed up a Worksheet Object with a Range object. That is, unless you actually have a worksheet named A1 which does not seem likely.

Range.CurrentRegion属性是一种很好的方法参考从A1辐射出来的不间断的细胞块.它继续向下和向右直到遇到完全空白的行和完全空白的列.单个或一小组中间空白单元格不会中断其对单元格块的引用.

The Range.CurrentRegion property is 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.

    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.

我相信使用常规的 Range.Sort方法会更好. .它比.AutoFilter.Sort更直接.随后的 AutoFilter方法完成该操作.

I believe you are better off with a conventional Range.Sort method. It is more straightforward than a .AutoFilter.Sort. A subsequent AutoFilter method finishes off the operation.

这篇关于VBA中的自动筛选排序的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！