VBA Sort and AutoFilter: Sort and Filter Data in Code

Part of the free Module 12: Excel VBA Course · Lesson 13 of 18 · Full Excel course

Sorting and filtering with VBA uses two Range methods: Sort arranges a table by one or more key columns, and AutoFilter shows only the rows that match a criterion, a pair of criteria or a colour. In this lesson you will learn the VBA Sort and AutoFilter syntax, how to sort by several columns, filter by one or two fields, use OR conditions and colour filters, and clear the filter again in code.

Table of Contents

Why sort and filter in code?

Every report that starts with an export needs the same preparation: sort by date or value, then filter to a region or a status. Doing it in VBA means the steps run identically each time and can be chained with copying, formatting and emailing. The Sort method takes a key range, an order (xlAscending or xlDescending) and a Header setting (xlYes, xlNo or xlGuess). AutoFilter takes the field number, counted from the left edge of the range, and one or two criteria written the way you would type them into a custom filter.

Sort a range by one column

The sample data has Date, Employee, Supervisor, Sales and Revenue in columns A to E. We sort by Revenue, column E, largest first.

Sales data with Date, Employee, Supervisor, Sales and Revenue columns before sorting with VBA — Data before sorting

Sub Sort_Data()
    Dim sh As Worksheet
    Set sh = ThisWorkbook.Sheets("Sheet1")
    sh.UsedRange.Sort Key1:=sh.Range("E1"), Order1:=xlDescending, Header:=xlYes
End Sub

Key1 names the column to sort by, Order1 sets the direction and Header:=xlYes keeps row 1 in place.

Data sorted by Revenue in descending order using the VBA Sort method — Data sorted by Revenue descending

Sort by multiple columns

Sub Sort_Data_Multi()
    Dim sh As Worksheet
    Set sh = ThisWorkbook.Sheets("Sheet1")
    sh.UsedRange.Sort Key1:=sh.Range("E1"), Order1:=xlDescending, _
                      Key2:=sh.Range("C1"), Order2:=xlAscending, Header:=xlYes
End Sub

Key2 breaks ties: rows with equal revenue are ordered by supervisor A to Z. Up to three keys are allowed here; for more, use the worksheet’s Sort object with SortFields.Add.

Filter with AutoFilter

We filter the same table to Supervisor-2. Supervisor is the third column of the range, so Field is 3.

Sales data before applying a VBA AutoFilter on the Supervisor column — Data before filtering

Sub Filter_Data()
    Dim sh As Worksheet
    Set sh = ThisWorkbook.Sheets("Sheet1")
    sh.UsedRange.AutoFilter Field:=3, Criteria1:="Supervisor-2"
End Sub

AutoFilter switches the filter arrows on if needed and applies the criterion to column 3 of the range.

Table filtered to Supervisor-2 rows by VBA AutoFilter — Filtered to Supervisor-2

Filter by two columns

Sub Filter_Two_Columns()
    Dim sh As Worksheet
    Set sh = ThisWorkbook.Sheets("Sheet1")
    sh.UsedRange.AutoFilter Field:=3, Criteria1:="Supervisor-2"
    sh.UsedRange.AutoFilter Field:=4, Criteria1:="<10"
End Sub

Each AutoFilter call adds a condition on another column; both must be true, so the result is Supervisor-2 rows with Sales below 10.

Table filtered to Supervisor-2 with Sales less than 10 — Filtered by two columns

Filter one column by two criteria (OR)

Sub Filter_Two_Criteria()
    Dim sh As Worksheet
    Set sh = ThisWorkbook.Sheets("Sheet1")
    sh.UsedRange.AutoFilter Field:=2, Criteria1:="EMP-1", Operator:=xlOr, Criteria2:="EMP-2"
End Sub

Operator:=xlOr joins two criteria on the same field; xlAnd is used for ranges such as “>=10” and “<=20”. For more than two values pass an array with Operator:=xlFilterValues.

Table filtered to employees EMP-1 or EMP-2 with the xlOr operator — Filtered to EMP-1 or EMP-2

Filter by colour

Data with yellow highlighted date cells before filtering by colour in VBA — Data with yellow cells to filter

Sub Filter_By_Color()
    Dim sh As Worksheet
    Set sh = ThisWorkbook.Sheets("Sheet1")
    sh.UsedRange.AutoFilter Field:=1, Criteria1:=vbYellow, Operator:=xlFilterCellColor
End Sub

With Operator:=xlFilterCellColor the criterion is a colour value, vbYellow or any RGB(); use xlFilterFontColor for font colours.

Table showing only the rows with yellow date cells after the colour filter — Filtered by yellow cell colour

Complete example: filter, copy the result and clear

Sub Extract_Supervisor_Report()
    Dim sh As Worksheet, rpt As Worksheet
    Dim data As Range

    Set sh = ThisWorkbook.Sheets("Sheet1")
    Set rpt = ThisWorkbook.Sheets("Report")
    Set data = sh.Range("A1").CurrentRegion

    If sh.AutoFilterMode Then sh.AutoFilterMode = False     'start clean

    data.Sort Key1:=data.Columns(5), Order1:=xlDescending, Header:=xlYes
    data.AutoFilter Field:=3, Criteria1:="Supervisor-2"
    data.AutoFilter Field:=4, Criteria1:=">=5"

    rpt.Cells.Clear
    data.SpecialCells(xlCellTypeVisible).Copy Destination:=rpt.Range("A1")
    rpt.Columns.AutoFit

    sh.AutoFilterMode = False                                'remove the filter
    MsgBox rpt.Cells(rpt.Rows.Count, 1).End(xlUp).Row - 1 & " rows extracted", vbInformation
End Sub

This runnable macro clears any old filter, sorts by revenue, applies two criteria, copies only the visible rows to a report sheet and removes the filter, leaving the source table exactly as it was.

Tips and common mistakes

Field is relative to the range, not the sheet. If the table starts in column C, Field 1 is column C.
Criteria are strings. Numbers need quotes with the operator, “>10”; dates are safest as “>=” & CLng(myDate).
Clear before re-filtering. sh.AutoFilterMode = False removes the arrows; sh.ShowAllData keeps them but shows every row (wrap in On Error Resume Next, it errors when nothing is filtered).
Sort returns nothing. It changes the range in place; use Range.Copy first if you need the original order.
Use the Sort object (sh.Sort.SortFields.Add) for colour sorts or more than three keys.

Practice and real-world use

Write a macro that asks for a supervisor with InputBox, filters the table to that name, copies the visible rows to a new workbook and saves it with the supervisor’s name. Automated distribution of regional extracts is one of the most common VBA tasks in any finance or sales team.

Related lessons

Frequently asked questions

How do I remove an AutoFilter in VBA?

Set sh.AutoFilterMode = False to remove the filter and the arrows, or call sh.ShowAllData to keep the arrows and show all rows. Check sh.FilterMode first to avoid an error when nothing is filtered.

How do I filter for more than two values in one column?

Pass an array of values with the xlFilterValues operator: rng.AutoFilter Field:=2, Criteria1:=Array(“EMP-1”, “EMP-2”, “EMP-3”), Operator:=xlFilterValues.

How do I sort by more than three columns in VBA?

Use the worksheet Sort object: sh.Sort.SortFields.Clear, then SortFields.Add for each key, set SetRange to the table, Header to xlYes and call Apply. It also supports sorting by cell or font colour.

Want the finished version? Ready-made Excel dashboards, trackers and VBA systems are available at NextGenTemplates.com.