excel - Auto Filter sort in VBA

Question

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.

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

Answer 1

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.

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.

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.

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.