<!– META H1: Excel VBA Autofilter: A Complete Guide with Examples Meta title (55 chars): VBA AutoFilter in Excel: Syntax, Criteria, and Examples Meta description (146 chars): Use the VBA AutoFilter method to filter by text, AND/OR, multiple values, top 10, and dates, plus Excel Tables, ShowAllData, and protected sheets. –>
Excel VBA Autofilter: A Complete Guide with Examples
A lot of Excel functionalities are also available to be used in VBA, and the Autofilter method is one such functionality.
If you have a dataset and you want to filter it using a criterion, you can easily do it using the Filter option in the Data ribbon.
And if you want a more advanced version of it, there is an advanced filter in Excel as well.
Then Why Even Use the AutoFilter in VBA?
If you just need to filter data and do some basic stuff, I would recommend sticking to the inbuilt Filter functionality that the Excel interface offers.
You should use VBA Autofilter when you want to filter the data as a part of your automation (or if it helps you save time by making it faster to filter the data).
For example, suppose you want to quickly filter the data based on a drop-down selection, and then copy this filtered data into a new worksheet.
While this can be done using the inbuilt filter functionality along with some copy-paste, it can take you a lot of time to do this manually.
In such a scenario, using VBA Autofilter can speed things up and save time.
In this tutorial, I’ll show you how the AutoFilter method works, with examples for text, number, and date filters, Excel Tables, and protected sheets.
Note: I will cover the examples on filtering data based on a drop-down selection and copying it into a new sheet later in this tutorial.
Follow along with the example file
VBA AutoFilter Examples.xlsm
Excel VBA Autofilter Syntax
Here is the syntax of the AutoFilter method:
Expression.AutoFilter(Field, Criteria1, Operator, Criteria2, SubField, VisibleDropDown)- Expression: This is the range on which you want to apply the auto filter.
- Field: [Optional argument] This is the column number that you want to filter. This is counted from the left in the dataset. So if you want to filter data based on the second column, this value would be 2.
- Criteria1: [Optional argument] This is the criteria based on which you want to filter the dataset. Use “=” to show blank cells and “<>” to show non-blank cells.
- Operator: [Optional argument] In case you’re using criteria 2 as well, you can combine these two criteria based on the Operator. The following operators are available for use: xlAnd, xlOr, xlBottom10Items, xlTop10Items, xlBottom10Percent, xlTop10Percent, xlFilterCellColor, xlFilterDynamic, xlFilterFontColor, xlFilterIcon, xlFilterValues
- Criteria2: [Optional argument] This is the second criteria on which you can filter the dataset.
- SubField: [Optional argument] This is used only with linked data types (such as Stocks or Geography), to filter on one of their fields. You can ignore it for regular data.
- VisibleDropDown: [Optional argument] You can specify whether you want the filter drop-down icon to appear in the filtered columns or not. This argument can be TRUE or FALSE.
Apart from Expression, all the other arguments are optional.
You can pass these arguments by position or by name.
Without names, you would have to keep every argument in its exact position and leave an empty slot for each one you skip. Naming them (such as Field:=2), as I have done in all the examples below, lets you pass only the ones you need.
The AutoFilter method works in the Windows desktop versions of Excel, including Microsoft 365. VBA doesn’t run in Excel for the web, and Microsoft’s documentation notes that Excel for Mac doesn’t support Range.AutoFilter.
In case you don’t use any argument, it would simply apply or remove the filter icons to the columns.
Sub ToggleFilterArrows()
Worksheets("Text Filter").Range("A1").AutoFilter
End Sub
The above code would simply apply the Autofilter method to the columns (or if it’s already applied, it will remove it).
This simply means that if you can not see the filter icons in the column headers, you will start seeing them when this above code is executed, and if you can see them, then they will be removed.
In case you have any filtered data, it will remove the filters and show you the full dataset.
Where to Put the VBA Code
All the macros in this tutorial (except two event codes, which I’ll cover in their own sections) go in a regular module.
Here are the steps to add a module and run a macro:
- Open the VB Editor (keyboard shortcut ALT + F11).
- Click Insert, then Module.
- Paste the code in the module code window.
- Place the cursor anywhere inside the macro and press F5 to run it.
You can also run a macro from the worksheet by pressing ALT + F8, selecting the macro name, and clicking Run.
When you save the workbook, save it as a macro-enabled file (.xlsm), or Excel will remove the code. In the example file, all these macros are already in a module called AutoFilterExamples.
Now let’s see some examples of using Excel VBA Autofilter that will make its usage clear.
Example: Filtering Data Based on a Text Condition
Suppose you have a dataset as shown below on the Text Filter sheet, and you want to filter it based on the ‘Item’ column.
The below code would filter all the rows where the item is ‘Printer’.
Sub FilterRows()
Worksheets("Text Filter").Range("A1").AutoFilter Field:=2, Criteria1:="Printer"
End Sub
The above code refers to the Text Filter sheet and within it, it refers to A1 (which is a cell in the dataset). It shows the 7 Printer rows and hides the rest.
Note that here we have used Field:=2, as the item column is the second column in our dataset from the left.
In the above code, I have used the sheet name. If you want the code to work on whichever sheet is active, you can use ActiveSheet instead of Worksheets(“Text Filter”).
If you want the opposite (all the rows except the Printer rows), put the not-equal-to sign (<>) before the text:
Sub FilterRowsNotEqual()
Worksheets("Text Filter").Range("A1").AutoFilter Field:=2, Criteria1:="<>Printer"
End Sub
This shows the 13 rows where the item is anything other than Printer.
Now if you’re thinking, why do I need to do this using a VBA code? This can easily be done using inbuilt filter functionality.
You’re right!
If this is all you want to do, better use the inbuilt Filter functionality.
But as you read the remaining tutorial, you’ll see that this can be combined with some extra code to create powerful automation.
But before I show you those, let me first cover a few examples to show you what all the AutoFilter method can do.
Example: Multiple Criteria (AND/OR) in the Same Column
Suppose I have the same dataset (on the OR Filter sheet), and this time I want to filter all the records where the item is either ‘Printer’ or ‘Projector’.
The below code would do this:
Sub FilterRowsOR()
Worksheets("OR Filter").Range("A1").AutoFilter Field:=2, Criteria1:="Printer", _
Operator:=xlOr, Criteria2:="Projector"
End Sub
Note that here I have used the xlOr operator.
This tells VBA to use both the criteria and filter the data if any of the two criteria are met. In our dataset, that gives 11 rows (7 Printer and 4 Projector).
Similarly, you can also use the AND criteria.
For example, if you want to filter all the records where the quantity is more than 10 but less than 20, you can use the below code (it works on the same dataset on the AND Filter sheet):
Sub FilterRowsAND()
Worksheets("AND Filter").Range("A1").AutoFilter Field:=4, Criteria1:=">10", _
Operator:=xlAnd, Criteria2:="<20"
End Sub
Here, Field:=4 points to the Quantity column, and the filter leaves the 9 rows with a quantity from 11 to 19.
Example: Filter More Than Two Values in a Column (Array with xlFilterValues)
The xlOr operator can only combine two values, because there are just two criteria arguments (Criteria1 and Criteria2). To filter on three or more values, you need to pass them as an array.
Below is the same dataset on the Multiple Values sheet, and I want to show the rows where the item is Printer, Projector, or Black Board.
Here is the code that will do this:
Sub FilterMultipleValues()
Worksheets("Multiple Values").Range("A1").AutoFilter Field:=2, _
Criteria1:=Array("Printer", "Projector", "Black Board"), _
Operator:=xlFilterValues
End Sub
The Array function holds the list of values you want to keep, and the xlFilterValues operator tells VBA to treat Criteria1 as that list. This shows the 14 rows where the item is any of the three.
This is the same as checking multiple boxes in the filter drop-down. You can add as many values to the array as you need.
Example: Multiple Criteria With Different Columns
Suppose you have the following dataset on the Multiple Columns sheet.
With Autofilter, you can filter multiple columns at the same time.
For example, if you want to filter all the records where the item is ‘Printer’ and the Sales Rep is ‘Mark’, you can use the below code:
Sub FilterMultipleColumns()
With Worksheets("Multiple Columns").Range("A1")
.AutoFilter Field:=2, Criteria1:="Printer"
.AutoFilter Field:=3, Criteria1:="Mark"
End With
End Sub
The first line filters the Item column, and the second line adds a filter to the Sales Rep column. Filters on different columns work together, so only the 2 rows that match both conditions stay visible.
Example: Filter Top 10 Records Using the AutoFilter Method
Suppose you have the below dataset on the Top 10 sheet, and you want to see the records with the highest quantity.
Below is the code that will give you the top 10 records (based on the quantity column):
Sub FilterRowsTop10()
Worksheets("Top 10").Range("A1").AutoFilter Field:=4, Criteria1:="10", _
Operator:=xlTop10Items
End Sub
Note that in this example, if you want to get the top 5 items, just change the number in Criteria1:=”10″ from 10 to 5.
So for top 5 items, the code would be:
Sub FilterRowsTop5()
Worksheets("Top 10").Range("A1").AutoFilter Field:=4, Criteria1:="5", _
Operator:=xlTop10Items
End Sub
It may look weird, but no matter how many top items you want, the Operator value always remains xlTop10Items.
Similarly, the below code would give you the bottom 10 items:
Sub FilterRowsBottom10()
Worksheets("Top 10").Range("A1").AutoFilter Field:=4, Criteria1:="10", _
Operator:=xlBottom10Items
End Sub
And if you want the bottom 5 items, change the number in Criteria1:=”10″ from 10 to 5.
Also note that if two rows tie at the cut-off value, Excel shows both of them. So in a dataset where the 10th and 11th largest quantities are the same, the top 10 filter shows 11 rows.
Example: Filter Top 10 Percent Using the AutoFilter Method
Suppose you have the same data set (as used in the previous examples), still on the Top 10 sheet.
Below is the code that will give you the top 10 percent records (based on the quantity column):
Sub FilterRowsTop10Percent()
Worksheets("Top 10").Range("A1").AutoFilter Field:=4, Criteria1:="10", _
Operator:=xlTop10Percent
End Sub
In our dataset, since we have 20 records, it will return the top 2 records (which is 10% of the total records).
Example: Using Wildcard Characters in Autofilter
Suppose you have a dataset as shown below on the Wildcard sheet, and you want to filter all the rows where the item name contains the word ‘Board’.
You can use the below code:
Sub FilterRowsWildcard()
Worksheets("Wildcard").Range("A1").AutoFilter Field:=2, Criteria1:="*Board*"
End Sub
In the above code, I have used the wildcard character * (asterisk) before and after the word ‘Board’ (which is the criteria).
An asterisk can represent any number of characters. So this would filter any item that has the word ‘board’ in it, which gives us the 9 White Board and Black Board rows.
Example: Filter Dates Between Two Dates
If you type a date as text inside the criteria, how Excel reads it can depend on the date settings of the computer running the macro. Giving the criteria the date’s serial number instead avoids that.
Below is the same dataset on the Date Range sheet, and I want to show only the sales from September 5, 2026 to September 10, 2026.
Here is the code that will do this:
Sub FilterDateRange()
Worksheets("Date Range").Range("A1").AutoFilter Field:=1, _
Criteria1:=">=" & CLng(DateSerial(2026, 9, 5)), _
Operator:=xlAnd, _
Criteria2:="<=" & CLng(DateSerial(2026, 9, 10))
End Sub
DateSerial builds each date from the year, month, and day, and CLng turns it into the date’s serial number (the number Excel stores behind every date).
The code uses Field:=1 (the Date column) with the xlAnd operator, so it shows the 5 rows dated from September 5 to September 10.
Example: Filter a Column by Its Header Name
So far, all the examples have used a fixed column number in the Field argument. That works until someone inserts or moves a column, and then Field:=3 quietly starts filtering the wrong column.
Below is the same dataset on the Header Name sheet, and I want to filter the ‘Sales Rep’ column to show only Laura’s rows, without hard-coding the column number.
Here is the code that finds the column by its header and then filters it:
Sub FilterByHeaderName()
Dim ws As Worksheet
Dim colNum As Variant
Set ws = Worksheets("Header Name")
colNum = Application.Match("Sales Rep", ws.Range("A1").CurrentRegion.Rows(1), 0)
If IsError(colNum) Then
MsgBox "There is no column named Sales Rep"
Exit Sub
End If
ws.Range("A1").AutoFilter Field:=colNum, Criteria1:="Laura"
End Sub
Application.Match looks for ‘Sales Rep’ in the header row of the dataset and returns its position (3 here). Since the data starts in column A, that position is the same as the Field number, and the filter shows Laura’s 4 rows.
If the header isn’t found, Application.Match returns an error value instead of stopping the code. The IsError check catches that and shows a message, so the macro never filters the wrong column.
Example: Copy Filtered Rows into a New Sheet
If you want to not only filter the records based on criteria but also copy the filtered rows, you can use the below macro.
It works on data that’s already filtered (manually or with any of the codes above). In the example file, the Copy Filtered Rows sheet is already filtered to show the 7 Printer rows, so you can run the macro right away.
Sub CopyFilteredRows()
Dim wsData As Worksheet
Dim wsNew As Worksheet
Set wsData = Worksheets("Copy Filtered Rows")
If Not wsData.FilterMode Then
MsgBox "There are no filtered rows"
Exit Sub
End If
Set wsNew = Worksheets.Add(After:=Worksheets(Worksheets.Count))
wsData.AutoFilter.Range.Copy wsNew.Range("A1")
Application.CutCopyMode = False
End Sub
The above code would check if there are any filtered rows in the Copy Filtered Rows sheet or not.
If there are no filtered rows, it will show a message box stating that.
The check uses FilterMode, which is TRUE only when a filter is actually hiding rows. A sheet can show filter arrows with nothing filtered, so checking for the arrows alone isn’t enough.
And if there are filtered rows, it will copy those, insert a new worksheet, and paste these rows on that newly inserted worksheet. Copying a filtered range copies only the visible rows, so the new sheet gets the header and the 7 Printer rows.
The last line clears the copy selection, so you don’t see the moving border around the data after the macro runs.
If you only need to know how many rows are visible after a filter, you can count the visible rows using VBA instead.
If you want to remove the filtered rows instead of copying them, see how to delete rows based on a cell value.
Example: Filter Data Based on a Cell Value (Drop-Down)
Using Autofilter in VBA along with a drop-down list, you can create a functionality where as soon as you select an item from the drop-down, all the records for that item are filtered.
Something as shown below:
Follow along with the example file
VBA AutoFilter Examples.xlsm
This type of construct can be useful when you want to quickly filter data and then use it further in your work.
In the example file, the Dropdown Filter sheet has the drop-down in cell B1 (with All and the four items), and the data starts in row 3.
Below is the code that will do this:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$B$1" Then
If Range("B1").Value = "All" Or Range("B1").Value = "" Then
If Me.FilterMode Then Me.ShowAllData
Else
Range("A3").AutoFilter Field:=2, Criteria1:=Range("B1").Value
End If
End If
End SubThis is a worksheet event code, which gets executed only when there is a change in the worksheet and the target cell is B1 (where we have the drop-down).
Also, an If Then Else condition is used to check if the user has selected ‘All’ from the drop down (or cleared the cell). If All is selected, the entire data set is shown.
For any other item, the code filters the Item column (Field:=2) of the data that starts in A3, using the value in B1 as the criteria.
This code is NOT placed in a module.
Instead, it needs to be placed in the backend of the worksheet that has this data.
Here are the steps to put this code in the worksheet code window:
- Open the VB Editor (keyboard shortcut ALT + F11).
- In the Project Explorer pane, double-click on the Worksheet name in which you want this filtering functionality.
- In the worksheet code window, copy and paste the above code.
- Close the VB Editor.
Now when you use the drop-down list, it will automatically filter the data.
If you’d rather not use a macro at all, the FILTER function can pull the matching rows into another range as soon as the drop-down changes (in Excel 365, 2024, and 2021). Here is how to extract data based on a drop-down selection.
Example: Filter an Excel Table Using VBA
If your data is in an Excel Table, you can filter it through the table itself instead of a cell address. The big advantage is that you can point to a column by its name.
Below is the same dataset, converted into an Excel Table named SalesData on the Excel Table sheet, and I want to show only the Printer rows.
Here is the code that will do this:
Sub FilterTable()
Dim lo As ListObject
Set lo = Worksheets("Excel Table").ListObjects("SalesData")
lo.Range.AutoFilter Field:=lo.ListColumns("Item").Index, Criteria1:="Printer"
End Sub
In VBA, an Excel Table is a ListObject. ListColumns(“Item”).Index returns the position of the Item column in the table (2 here), so the code keeps working even if someone adds or moves columns.
To clear the table’s filters and show all the rows again, use this code:
Sub ClearTableFilter()
Worksheets("Excel Table").ListObjects("SalesData").AutoFilter.ShowAllData
End SubThe worksheet’s ShowAllData method stops with error 1004 when nothing is filtered, but this table version doesn’t, so you can run it anytime.
Turn Excel AutoFilter ON/OFF Using VBA
When applying Autofilter to a range of cells, there may already be some filters in place.
You can use the below code to turn off any pre-applied auto filters:
Sub TurnOFFAutoFilter()
Worksheets("Text Filter").AutoFilterMode = False
End Sub
This code checks the entire sheet and removes any filters that have been applied. It also removes the filter arrows.
This code works on the sheet rather than a range, which is fine because a worksheet can have only one AutoFilter range. And if AutoFilter is already off, the code simply does nothing.
Important: AutoFilterMode = False doesn’t touch the filters in an Excel Table, so the filtered rows stay hidden. For a table, use the ClearTableFilter code from the previous section.
Similarly, if you want to turn on AutoFilter, use the below code:
Sub TurnOnAutoFilter()
If Not Worksheets("Text Filter").AutoFilterMode Then
Worksheets("Text Filter").Range("A1").AutoFilter
End If
End Sub
The above code checks whether the filter arrows are already there. If they are not, it adds them, else it does nothing.
The check matters because AutoFilter without arguments is a toggle. Running it on a sheet that already has the arrows would remove them.
Also note that you can’t check for filters with a line like If Range(“A1”).AutoFilter Then. That line runs the AutoFilter method (and toggles the arrows) just to get a value, so use AutoFilterMode instead.
Check if AutoFilter is Already Applied
If you have a sheet with multiple datasets and you want to make sure you know that there are no filters already in place, you can use the below code.
Sub CheckforFilters()
With Worksheets("Text Filter")
If .FilterMode Then
MsgBox "This sheet has filtered data"
ElseIf .AutoFilterMode Then
MsgBox "The filter arrows are on, but nothing is filtered"
Else
MsgBox "There are no filters on this sheet"
End If
End With
End SubThis code uses a message box function that tells you which of three states the sheet is in.
FilterMode is TRUE when a filter is hiding rows, and AutoFilterMode is TRUE when the filter arrows are showing. So the code first checks for filtered data, then for the arrows, and otherwise tells you there are no filters.
Show All Data (and Avoid the ShowAllData Error)
If you have filters applied to the dataset and you want to show all the data, use the below code:
Sub ShowAllData()
With Worksheets("Text Filter")
If .FilterMode Then .ShowAllData
End With
End SubThe above code checks whether the FilterMode is TRUE or FALSE.
If it’s true, it means a filter has been applied and it uses the ShowAllData method to show all the data.
You need that check, because running ShowAllData on a sheet where nothing is filtered stops the macro with run-time error 1004 (Method ‘ShowAllData’ of object ‘_Worksheet’ failed).
Note that this does not remove the filters. The filter icons are still available to be used.
This is different from the TurnOFFAutoFilter code, which removes the arrows as well.
Using AutoFilter on Protected Sheets
By default, when you protect a sheet, the filters won’t work.
In case you already have filters in place, you can enable AutoFilter to make sure it works even on protected sheets.
To do this, check the Use AutoFilter option while protecting the sheet.
While this works when you already have filters in place, in case you try to add or change Autofilters using a VBA code, it won’t work.
Since the sheet is protected, the macro stops with run-time error 1004, which says you cannot use this command on a protected sheet.
So you need to use a code to protect the worksheet and make sure auto filters are enabled in it.
This can be useful when you have created a dynamic filter (something I covered in the example ‘Filter Data Based on a Cell Value (Drop-Down)’).
Below is the code that will protect the sheet, but at the same time, allow you to use Filters as well as VBA macros in it.
Private Sub Workbook_Open()
With Worksheets("Protected Sheet")
.EnableAutoFilter = True
.Protect Password:="password", Contents:=True, UserInterfaceOnly:=True, _
AllowFiltering:=True
End With
End SubThis code needs to be placed in ThisWorkbook code window.
Here are the steps to put the code in ThisWorkbook code window:
- Open the VB Editor (keyboard shortcut ALT + F11).
- In the Project Explorer pane, double-click on the ThisWorkbook object.
- In the code window that opens, copy and paste the above code.
As soon as you open the workbook and enable macros, it will run the macro automatically and protect the Protected Sheet.
However, before doing that, it will specify ‘EnableAutoFilter = True’, which means that the filters would work in the protected sheet as well.
Also, it sets the ‘UserInterfaceOnly’ argument to ‘True’. This means that while the worksheet is protected, the VBA macros code would continue to work.
The AllowFiltering argument is the code version of the Use AutoFilter checkbox, so you can also change the filters yourself using the filter arrows.
The reason this code sits in Workbook_Open is that the UserInterfaceOnly setting isn’t saved with the file. When you reopen the workbook, the sheet is fully protected again, so the code re-applies the setting every time the workbook opens.
To test it, open the example file with macros enabled and run this macro, which filters the protected sheet without any error:
Sub FilterProtectedSheet()
Worksheets("Protected Sheet").Range("A1").AutoFilter Field:=2, Criteria1:="Printer"
End Sub
The password in this code is “password”, so change it to your own before you use it on a real file. For more ways to do this with code, see how to protect and unprotect sheets using VBA.
So these are some of the ways you can use the AutoFilter method in VBA.
You can combine them with your own code to automate the filtering you do regularly.
I hope you found this tutorial useful.
Other Excel Articles You May Also Like:
your site is very helpfull to me!
Is it possible to make the Field number dynamic?
In my case the column number changes when a new date is created.
The column was for instance 10 and now 11 or higher.
Hi how would you autofilter alphanumeric values in a column, examples are
column 1 has A3 Cell contains the value-Joe 2516, A4 has value- Jenny2517 Cell A5 has 2875Ani, Cell A6 has 1092-Naomi
Is it possible to get some al the values out column 3 after filtering on a other sheet
This is truly a remarkable website for Excel VBA tutorials and it has helped me enormously in trying to automate searches and filters in my personal book catalogue. But of course I have a question 🙂 which I hope will provide a tutorial for us all to appreciate. My book catalogue is an Excel worksheet which I convertd to an Excel Table to which I have applied a top row of buttons that apply filters on column 3 with each button filtering book types like Travel, History, Psychology, Psychodratic Bodywork, etc. I would like to use VBA to total and display the result of each search?
Hi, I want to know if its possible to filter by a week number, as example having week number in a cell.
Thanks.
it’s a great macro. keep it ahead
Something I’m not clear about. I always see these filters with 1 or 2 criteria. Is it possible to have 3+ criteria, e.g.
With Worksheets(“Sheet1”).Range(“A1″)
.AutoFilter field:=2, Criteria1:=”Printer”
.AutoFilter field:=3, Criteria1:=”Mark”
.AutoFilter field:=5, Criteria1:=”Fax”
.AutoFilter field:=9, Criteria1:=<15
End With
Also, is it possible to have several .AutoFilters on the same field without using Criteria2, i.e.
With Worksheets("Sheet1").Range("A1")
.AutoFilter field:=2, Criteria1:="Printer”
.AutoFilter field:=2, Criteria1:=”Fax”
.AutoFilter field:=2, Criteria1:=”Copier”
.AutoFilter field:=9, Criteria1:=<15
End With
I have a case where I want to filter out text that matches any of several long boilerplate strings.
Also, can Criteria1 (or 2) use a UDF?
yes it’s Possible
this will let you add more than 2 Criteria in the same Field
.AutoFilter 2, Criteria1:=Array(“Printer”, ”Fax”, ”Copier”), Operator:=xlFilterValues
I need to filter the data for rows that match a value entered in another cell. You have an example using a drop down, but I don’t want to use a drop down. Techs would use the shared file, enter their tech number and see their work listed in the spreadsheet. I don’t want them being able to pull up other tech’s data using a drop down.
Thank you, Sumit. This tutorial is very clear and easy to follow.
Thanks Bill.. Glad you found it useful!
Very nice tutorial, but why did you protect the example file?
If you share an example file, usually it’s because you want people to see how it was done.
I have no use for a locked example file.
Ok, I didn’t read through the end, my bad!
How do you change the code to filter for all items but not 1 specific (or 2 for that matter) i.e. if i tried to select all items apart from ‘Opening Balance’ I tried the below but it wont work
Worksheets(“Sheet1”).Range(“A1″).AutoFilter Field:=2, Criteria1:=”Opening Balance”
Is there a way to instruct VBA to select all items but not (different than) something?
I want to use filter option for a group of excel worksheets. The sheets contain same column heading e.g. Name, Address, post code, phone number etc. I want to use filtering option to be applied for all sheets at a time. Filtering one by one sheet is time consuming. So please help me on this point.
sipppp, tanks so much
Hi the above example really worth. But we need how to convert DAT. File into an excel file from folder, from there how to apply filter criteria and the output saved different workbook
Tutorial is excellent , wanted to know how to go to last cell of the filtered column in macro as we will never know where my last cell is , so how to get to that cell for further work
very good tutorial
Amazing, thank you very much, this is very helpful article.
In case several options need to be chosen from a column (not just one, or two), ARRAY could be used. Would it be possible to add this exemple into your article ?
That’s what I’m looking for as well. Two criteria is often just not enough, an array would be perfect.
Simply fantastic work, very well done, Sir !