Filters & Shortcut Keys

Module 7 of 9

REMINDER: To fully understand Filters & Shortcut Keys, you should have already watched the Filters and Shortcut Keys (Option 2) Video in Canvas and followed along with the Macro Demo file. This practice will build upon that foundation.

Filters and Shortcut Keys in VBA

So far you've processed data by looping through every record and using an IF statement to act on the ones you want. That's Option 1 — it's readable, easy to debug with F8, and works for any condition. Option 2 takes a different approach: filter the data to only the records you want, then copy or process all of them at once. Both approaches produce the same result. Option 2 is faster for large datasets. Option 1 is easier to follow and debug. Knowing both gives you flexibility.

This module covers the Option 2 techniques: AutoFilter to show only matching rows, the FILTER function to extract records into a new location, PasteSpecial to break formula links, and CountA to count results without hardcoding numbers. These are the tools from the Filters and Shortcut Keys demo video.

Option 1 is what the Step-Through and Build videos focus on. Option 2 shows up in the Filters demo. The exam may ask you to identify which approach a given piece of code is using — know the signature patterns of each.

AutoFilter

AutoFilter shows only the rows that match a condition and hides the rest. In VBA you apply it to a table using ListObjects and specify which field (column number) and which criteria to filter on. After filtering, only matching rows are visible — you can then copy, delete, or count them. Always remove the filter when you're done.

Syntax

' Apply AutoFilter — Field is the column number in the table
ActiveSheet.ListObjects("TableName").Range.AutoFilter Field:=8, Criteria1:="Deny"

' Delete visible (filtered) rows
ActiveCell.Rows("1:1").EntireRow.Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Delete Shift:=xlUp

' Remove the filter
ActiveSheet.ListObjects("TableName").Range.AutoFilter Field:=8

This is the Option 2 pattern from the Project Demo — filtering for denied students and deleting them:

' FILTER for Denied Students
ActiveSheet.ListObjects("ApplicantData").Range.AutoFilter Field:=8, _
    Criteria1:="Deny"

' DELETE Denied Students
ActiveCell.Rows("1:1").EntireRow.Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Delete Shift:=xlUp

' Remove filter
ActiveSheet.ListObjects("ApplicantData").Range.AutoFilter Field:=8

FILTER Function in a Cell

The FILTER function extracts matching records from a table into a new location as a formula. In VBA you write it as a string into a cell using Formula2R1C1. The result is a dynamic array — it spills into as many rows as there are matching records. Because it's a formula, you usually follow it with a copy/PasteSpecial step to convert the results to values so they're no longer linked to the original data.

Syntax

' Write FILTER formula into a cell
Range("A3").Formula2R1C1 = "=FILTER(TableName, TableName[Field]=""Value"",)"

' Copy the results
Range("A3").Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.Copy

' PasteSpecial Values — breaks formula link
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone

PasteSpecial Values

When you copy cells that contain formulas and paste them normally, the pasted cells still contain formulas linked to the original data. PasteSpecial with xlPasteValues pastes only the results — plain numbers and text with no formulas. This is essential after using FILTER or any other formula-based extraction, because you want the data to stand on its own without depending on the source table.

Syntax

Selection.Copy

Selection.PasteSpecial Paste:=xlPasteValues, _
    Operation:=xlNone, _
    SkipBlanks:=False, _
    Transpose:=False

CountA to Count Results

After filtering or copying data, you often need to know how many records you ended up with. CountA counts non-blank cells — use it on a full column after your filter or paste operation to get the count without hardcoding a number. Subtract 1 if your range includes the header row.

Syntax

' Count non-blank cells in column A (includes header)
NumberAccepted = Application.WorksheetFunction.CountA(Range("A:A")) - 1

' Or count just the data rows
NumberAccepted = Application.WorksheetFunction.CountA(Range("A2:A1000"))

Quick Check

1. What is the main difference between Option 1 (Loop + IF) and Option 2 (Filter + Copy)?

2. After applying AutoFilter, you want to delete all visible rows. What must you do after the deletion?

3. Why do you use PasteSpecial Values after copying FILTER results?

4. You filter the ApplicantData table for "Accept" and copy the 20 visible rows to a new sheet named "Accepted Students". On that new sheet, what does Application.WorksheetFunction.CountA(Range("A:A")) return?

5. Which approach is easier to debug using F8?

If an exam question shows you a macro and asks what it does, look for the signature patterns: a Do Until loop with IF = Option 1, AutoFilter or FILTER function = Option 2.

Easy Wins

Apply and Remove AutoFilter Guided

Apply AutoFilter to the Aggie Advisors data to show only accepted students, then count and remove the filter.

Use the Aggie Advisors data from the data table below. Make sure it's in a table named ApplicantData on Sheet1. If your table has a different name, update the code accordingly.

CountA Observation Observation

Run this standalone macro on your Aggie Advisors data. It filters for accepted students, counts the visible rows, displays the count, then removes the filter — all in one pass. Before running, predict: what number will the MsgBox show?

Option Explicit
Sub CountAccepted()
    Dim AcceptCount As Integer

    Sheets("Sheet1").Select

    ActiveSheet.ListObjects("ApplicantData").Range.AutoFilter Field:=8, _
        Criteria1:="Accept"

    AcceptCount = Application.WorksheetFunction.CountA(Range("A:A")) - 1
    MsgBox "Accepted students: " & AcceptCount

    ActiveSheet.ListObjects("ApplicantData").Range.AutoFilter Field:=8
End Sub

Practice Problem

Copy this data and paste it into cell A1 of a new Excel worksheet. Press Ctrl+V — Excel will split the columns automatically. Make sure the data is formatted as a table named ApplicantData.

Filter and Copy Accepted Students

Using the Aggie Advisors data, write a macro that uses the Option 2 approach to isolate accepted students and copy them to a new sheet.

What your macro needs to do:

  • Navigate to Sheet1 where ApplicantData table lives
  • Apply AutoFilter to ApplicantData, Field 8, Criteria1:="Accept"
  • Select the visible data rows (not the header) and copy them
  • Create or navigate to a sheet named "Accepted Students"
  • PasteSpecial Values to paste without formula links
  • Navigate back to Sheet1 and remove the AutoFilter
  • Count the pasted records on "Accepted Students" using CountA and display: "Accepted students copied: [X]"

Expected result: 20 rows on the Accepted Students sheet. MsgBox shows "Accepted students copied: 20"

See this in the Aggie Advisors project →

Challenge

Compare Both Approaches

No hints. No steps. This is exam level.

Write two macros that produce the same result using different approaches:

Macro 1 — Option1_Count: Use a Do Until loop with an IF statement to count accepted students. Display the count in a MsgBox.

Macro 2 — Option2_Count: Use AutoFilter to filter for accepted students, use CountA to count them, remove the filter, and display the count in a MsgBox.

Both macros must display the same number.

After writing both, add a comment at the top of each macro explaining in one sentence when you would choose that approach over the other.

Expected result: Both MsgBoxes show 20.

See this in the Aggie Advisors project →