VBA AutoFilter for Tanaka: Filter Excel Rows by Name

VBA & macros · Updated November 18, 2026

To show only rows whose name is Tanaka, apply Excel’s AutoFilter to a range that includes its header row, then set the matching column’s filter criterion to Tanaka. In VBA, the essential instruction is rng.AutoFilter Field:=3, Criteria1:="Tanaka" when Tanaka is in the third column of the filtered range; the field number is relative to that range, not necessarily the worksheet column number.

What “VBA AutoFilter Tanaka” Usually Means

This search usually refers to a practical Excel task: use a VBA macro to filter a list so that records for a person named Tanaka remain visible. AutoFilter is Excel’s built-in feature for hiding rows that do not meet selected criteria. It does not delete the other rows or change their values. A user can still clear the filter and see the complete list.

For example, a worksheet might contain a table with columns named Department, Date, Employee, and Hours. If Employee is the third column in the range, filtering that field for Tanaka displays records whose Employee value matches Tanaka. If Employee is actually the first column in the selected range, the Field argument must be 1, even if that column is column C on the worksheet.

“Tanaka” is a sample criterion, not a special VBA keyword or Excel function. Replace it with the actual name, value, or text your workbook uses. The header text itself is not passed as the Field argument; VBA uses the field’s position inside the range.

How AutoFilter Works in VBA

The AutoFilter method applies a filter to a range with a header row. Its Field argument identifies which column to filter, and Criteria1 supplies the first criterion. For a simple text match, Criteria1 can be a string such as Tanaka. Excel’s text filtering is generally not case-sensitive, so Tanaka and TANAKA are treated as equivalent for this purpose.

Here is a complete example for a worksheet where the data occupies A1:D100, row 1 contains headers, and Employee is in column C:

Sub FilterForTanaka()
    Dim ws As Worksheet
    Dim dataRange As Range

    ' Set the worksheet and include the header row.
    Set ws = ThisWorkbook.Worksheets("Data")
    Set dataRange = ws.Range("A1:D100")

    ' Employee is the third column within A:D.
    dataRange.AutoFilter Field:=3, Criteria1:="Tanaka"
End Sub

Change Data to the name of the sheet in your workbook and adjust the range to cover your real headers and records. The filter range should normally include every column that belongs to the list, not just the column being filtered. Including the header lets Excel display filter controls and interpret the field position correctly.

Before running any macro, save a copy of the workbook. VBA macros cannot be undone with Ctrl+Z, even when the macro only changes a filter. This particular macro does not overwrite cell values, but a saved copy is still a sensible precaution when running unfamiliar code.

Finding the Correct Field Number

The most common source of a wrong result is counting worksheet columns instead of columns in the specified range. If the range starts in A, worksheet column C is field 3. If the range starts in B, worksheet column C is field 2. If the range starts in C, worksheet column C is field 1. The field number always starts at the left edge of the range you pass to AutoFilter.

Use the header row to map positions before writing the macro. For instance, with headers in B1:E1 reading Date, Employee, Department, and Hours, Employee is field 2 in B:E. Passing Field:=3 would filter Department instead. A quick check of the range address and its header order can prevent a macro that runs without an error but filters the wrong information.

  • Include the header row as the first row of the range.
  • Count the filtered column from the range’s left edge, starting at 1.
  • Keep the range rectangular, with one header per column.
  • Check that the header row does not contain merged cells or blank headings that make the list ambiguous.

Choosing a Range: Fixed Lists and Excel Tables

A fixed address such as A1:D100 is straightforward when the dataset always has the same size. It can become unreliable when new records are added below row 100, because those records will not be included. If the list changes often, use an Excel Table: select the data, then choose Insert > Table and confirm that the table has headers. Tables expand as records are added and provide a more dependable range for a recurring macro.

A table can be addressed through its ListObject object. In the example below, the table is named EmployeeData and Employee is its second column. Table names and column order must match the workbook.

Sub FilterEmployeeTableForTanaka()
    Dim ws As Worksheet
    Dim employeeTable As ListObject

    ' Set the sheet and the existing Excel Table.
    Set ws = ThisWorkbook.Worksheets("Data")
    Set employeeTable = ws.ListObjects("EmployeeData")

    ' Employee is the second column in this table.
    employeeTable.Range.AutoFilter Field:=2, Criteria1:="Tanaka"
End Sub

Filtering the table’s range keeps the field number tied to the table’s column order, not to the table’s worksheet position. A table is often preferable for data that grows, but it does not fix incorrect headers or an incorrect Field value. If you rename or reorder table columns, review the macro as well.

Exact Names, Partial Matches, and Cell-Based Criteria

For a straightforward exact text criterion, use Criteria1:=”Tanaka”. If the list contains values such as Tanaka Haru and Haruto Tanaka and you want entries containing the text Tanaka anywhere, use wildcard characters. An asterisk stands for any sequence of characters; a question mark stands for one character. For example, Criteria1:=”*Tanaka*” finds text with Tanaka somewhere in the cell. A criterion of Tanaka* finds text beginning with Tanaka.

Wildcards broaden the match and may return more rows than an exact name search. They are useful for inconsistent naming or searching a longer description, but they can also include unintended values. If the data has separate first-name and last-name fields, filtering the dedicated surname column is generally clearer than searching a combined text field.

To avoid hard-coding the name, read it from a worksheet cell. This example assumes the data is in A1:D100, Employee is the third field, and the requested name is in F1:

Sub FilterForNameInCell()
    Dim ws As Worksheet
    Dim dataRange As Range
    Dim nameToFind As String

    ' Read the requested name from F1.
    Set ws = ThisWorkbook.Worksheets("Data")
    Set dataRange = ws.Range("A1:D100")
    nameToFind = CStr(ws.Range("F1").Value)

    ' Apply the value as the Employee criterion.
    dataRange.AutoFilter Field:=3, Criteria1:=nameToFind
End Sub

Check that F1 is not blank before using this approach. A blank criterion can behave differently from an intended name search and may filter for blank cells. You can add an If statement that exits with a message when the input is empty. Also confirm that the cell contains the intended value, without accidental leading or trailing spaces. If the source data contains extra spaces, a search for Tanaka may not match a value stored as Tanaka followed by a space.

Clearing or Changing the Filter

Running the same filter again with a different criterion replaces the criterion for that field. To clear all active filters from the sheet’s list while leaving AutoFilter controls available, use ShowAllData only when the worksheet is currently filtered. Calling it when no filter is active can raise an error, so check the worksheet’s FilterMode first.

Sub ClearSheetFilters()
    Dim ws As Worksheet

    Set ws = ThisWorkbook.Worksheets("Data")

    ' Show all rows only if a filter is currently hiding some.
    If ws.FilterMode Then
        ws.ShowAllData
    End If
End Sub

Clearing the current criteria is different from turning off the filter arrows. In the Excel interface, use Data > Clear to remove filter criteria, or use Data > Filter to toggle the filter controls. The exact effect of VBA depends on whether the range is a normal list or an Excel Table, so avoid removing AutoFilter settings when the goal is only to reveal all records.

Common Problems and How to Fix Them

If the macro runs but no rows appear, first verify that the criterion exists in the intended column. Check spelling, spaces, the actual Field number, and whether the selected range includes the relevant records. Text stored with extra spaces or a different spelling will not be the same value as Tanaka. If the data combines a surname and given name, an exact match for Tanaka will not match a longer entry such as Tanaka Haru; use an appropriate wildcard criterion if that is what you intend.

  • Wrong column filtered: Recount Field from the left edge of the range, not from worksheet column A.
  • New records are ignored: Expand the fixed range or convert the list to an Excel Table.
  • Header appears as a record: Make sure the range starts at the header row and that the first row is actually structured as headers.
  • Macro cannot find the worksheet or table: Confirm the sheet tab name and table name exactly match the names used in the code.
  • Macro is blocked: Save the workbook in a macro-enabled format such as .xlsm, and follow your organization’s macro security policy.
  • ShowAllData causes an error: Call it only when a filter is active, for example after checking FilterMode.

Also consider whether another field already has a filter applied. AutoFilter criteria on different fields normally combine, so a Tanaka filter on Employee can still show no rows if another column excludes every matching record. Clear other criteria or inspect each filtered column before concluding that the name is absent.

Running the Macro in Excel for Microsoft 365

On Windows, open the Visual Basic Editor with Alt+F11. In the editor, choose Insert > Module, paste the procedure into the module, and update the sheet name, range, and field number. Return to Excel and run the macro from Developer > Macros. If the Developer tab is not visible, enable it through File > Options > Customize Ribbon by selecting Developer. The keyboard shortcut Alt+F8 opens the Macro dialog in Excel for Windows.

On a Mac, VBA procedures such as these use the same core AutoFilter method, but keyboard shortcuts and some interface details differ. Open the Visual Basic Editor using the shortcut configured for your Excel installation, or use the Developer tab if available. On both platforms, the Data tab’s Filter command can help you inspect the result manually. Macro availability may also depend on workbook format and your organization’s security settings. Excel for the web can open a workbook containing VBA, but it cannot create, edit, or run VBA macros; use the desktop Excel application to run this code.

When AutoFilter Is Not the Right Tool

AutoFilter is appropriate when you want to hide nonmatching records in place while keeping the original list intact. It is convenient for reviewing a large table, selecting visible records, or building a small macro that applies a repeatable view. It is not a way to permanently remove other records: clearing the filter makes them visible again.

If you need a separate extracted list that updates as worksheet values change, Excel’s FILTER function may be a better fit in Microsoft 365 or Excel 2021 and later. That is a worksheet formula, not VBA, and it returns matching results into another area rather than hiding rows in the original list. For a one-time inspection, the standard Data > Filter controls may be simpler than writing a macro. Choose VBA when the filtering is one step in a larger repeatable process, or when a button or macro needs to apply the same criterion reliably.

Frequently Asked Questions

Is Tanaka a special AutoFilter keyword?
No. Tanaka is simply the text value used as a filter criterion. Replace it with the name or value you want to find in the selected field.

Does Field:=3 mean worksheet column C?
Not necessarily. It means the third column within the range passed to AutoFilter. It corresponds to worksheet column C only when the range begins in column A.

Will the filter find Tanaka in a longer entry?
An exact criterion of Tanaka is for a cell matching that value. To find text that contains the name, use a wildcard criterion such as *Tanaka*. That can also match other text containing the same character sequence.

Does AutoFilter delete the other rows?
No. It hides rows that do not meet the active criteria. Clear the filter to display the full list again; the macro shown here does not delete records.

Can I run this VBA in Excel for the web?
No. Excel for the web can preserve a workbook containing VBA, but VBA macros must be run in the desktop Excel application. The same basic AutoFilter code works in desktop Excel for Windows and Mac, although the editor and keyboard shortcuts can differ.

Related

Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.

More in VBA & macros

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.