Anyone expert in understanding auto filter in VBA ?

[nick323f]

Hi all,

I need some help with confidence in the following code.
I have a data set which is given to me in the wrong order. So I need to sort the data in ascending order, by the content of column B. The data starts from row 8

Each row contains a set of measured values from an automated test of one device, with each cell containing the results of one measurement. Thus it is vital that the data in each row stays together.
The problem is that the data set has a variable number of rows, and columns, and the data set might contain empty columns and/or there might be some columns which only have data in some cells.
My fear is that, because there are blank columns and/or blank cells in the data, the auto filter and sort ascending process might occasionally pick up and sort only some of the columns. This would would mix the data meaning I could not subsequently identify which results can from which device.

I'm sorry to say that I built the following code using the auto macro recorder in Excel, because I don't have a very clear understanding of the variables in the AutoFilter command.
If anyone has a really clear understanding of this command and can see any possible way my fears could occur then can you please let me know?

Many thanks
Nick

Sub test()
'
' test Macro
'
    Sheets("Data file").Select
    Range("b7").EntireRow.Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.AutoFilter
    ActiveWorkbook.Worksheets("Data file").AutoFilter.Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Data file").AutoFilter.Sort.SortFields.Add Key:= _
        Range("B7"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption _
        :=xlSortNormal
    With ActiveWorkbook.Worksheets("Data file").AutoFilter.Sort
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    Selection.AutoFilter
    Range("A1").Select
End Sub

 

[rlv01]

It's not clear to me how you want to use autofilter, but this example will sort the data and turn on autofilter (but not set any filter criteria)

Sub Test()    
    Dim WB As Workbook    
    Dim WS As Worksheet
    Dim SortRange As Range

    Set WB = ThisWorkbook
    Set WS = WB.Worksheets("Data file")

   'Turn off AutoFilter
    If WS.AutoFilterMode Then
        WS.AutoFilterMode = False
    End If

    'Define the range of the data to be sorted
    Set SortRange = Application.Intersect(WS.UsedRange, WS.Range("B7:B" & WS.Range("B" & WS.Rows.Count).End(xlUp).Row).EntireRow)
    Debug.Print SortRange.Address
    
    'Sort the data
    SortRange.Sort Key1:=WS.Range("B7"), Order1:=xlAscending, Header:=xlYes, DataOption1:=xlSortNormal

    'Turn on AutoFilter
    SortRange.AutoFilter
End Sub