VBA advanced filtering is used for more complex filtering needs that the AutoFilter in Excel cannot complete. You can filter out unique items, extract specific words or dates and even copy them to another document or sheet. VBA advanced filtering is used for more complex filtering needs that the AutoFilter in Excel cannot complete. You can filter out unique items, extract specific words or dates and even copy them to another document or sheet.
You can filter the data in a table to show only the data you’re interested in seeing. You filter data by creating rules that determine which rows in a table are visible.
For example, if you’re looking at a table of possible monthly mortgage payments for a loan with various interest rates, you can filter the table to show only the loans you can afford.
Note: If the filter options aren’t available, your table might have cells merged across adjacent rows. You need to unmerge the cells to filter data. Click the merged cell, then choose Table > Unmerge Cells (from the Table menu at the top of your screen). If you’re not sure where the merged cells are, select the table, then choose Table > Unmerge All Cells.
Filter rows
You can quickly filter a table based on one of the values in a column.
- Select a column or cell, then click the arrow on the column’s border.
- Choose Filter Table, then choose the value you want to filter by (for example, freshman in a table with data about high school students).All rows without the freshman value in the selected column are hidden.To remove the filter, choose the value again.
![Mac Mac](https://www.ablebits.com/_img-blog/_uploads/advanced-filter.png)
Create a filtering rule
You can create filtering rules based on the values in a column. Only rows with the specified values in that column appear.
- Click the table.
- In the Organize sidebar, click the Filter tab.
- Click Add a Filter, then choose which column to filter by.
- Click the type of filter you want (for example, Text), then click a rule (for example, “starts with”).The filtering rule appears in the Filter tab.
- Enter values for your rule; for example, if you select “is not,” type text such as “due by.”The Filters checkbox is selected and the rule is applied to the table.
- To add another rule for the same column, click Or, then choose a new filtering rule.You can have multiple rules for a column, for example, “Show rows that have ‘yes’ or ‘maybe’ in Column C.”
- To add a filter to a different column, click Add a Filter and enter another filtering rule.If a table has multiple filtering rules, you can choose whether to show rows that match all filters or any filter in the pop-up menu at the top.
Note: You can’t add new rows to the table until you turn off filtering. To turn off filtering, deselect the Filters checkbox in the Filter tab.
Turn off filters or delete a filter
You can turn off all filters for a table without deleting them. You can turn them back on later if necessary. If you don’t need a filter, you can delete it.
- Click the table, then in the Organize sidebar, click the Filter tab.Use the controls as follows: