Remove All Filtering From a Worksheet in Excel

Add to Favorites
This Excel macro removes all filtering from a worksheet in Excel. This allows you to completely remove any filter now matter how complex from any worksheet in Excel. This means that the entire original data set will then be visible in Excel.

How Filter Macros Work

All of the elements below often appear within autofilter macros.

Range
    This should be the start of the data set, table, or list which you would like to filter. This can also be the entire range reference to the table. For example, if the table was from cell A1:D450, you could put that as the range or you could put A1 as the range. You can do this because the autofilter feature in Excel will automatically try to determine the total range which you would like to filter.
Field
    This is the number of the column within the data table that you would like to filter. The first field (1) is the very first column in the data set that will be filtered. This means that if your data starts in column B and your range is Range("B1") and you want to filter based on column D, you would put 3 in for the field.
Criteria
    This is the criteria by which you would like to filter. Some of the macros have symbols within the quotation marks after this argument and those symbols (such as <,>,*,?, etc.) should be left where they are in order to retain the functionality of the macro.
Operator
    You will not have to change this. This is simply the way to perform different types of filter features in Excel.

Where to install the macro:  Module

Excel Macro to Remove All Filtering From a Worksheet in Excel

Sub AutoFilter_Remove()
'This macro removes any filtering in order to display all of the data but it does not remove the filter arrows

ActiveSheet.ShowAllData

End Sub







Similar Content on TeachExcel
Delete All Empty Rows or Blank Cells from a Range in Excel
Tutorial: How to quickly delete all empty cells or rows from a range in Excel.  This allows you to q...
Remove Gridlines from Only Part of a Worksheet in Excel
Tutorial: How to remove gridlines from a part of a worksheet in Excel instead of the entire workshe...
Print Only Specific Parts of a Worksheet in Excel
Tutorial: In Excel you can select parts of a worksheet to print while ignoring all of the other data...
Remove All Data Validation from a Cell in Excel
Macro: Remove all data validation from a cell in Excel with this free Excel macro. This is a...
Quickly Move Cells Around a worksheet in Excel Cut/Paste Trick
Tutorial: Moving Cells around a worksheet in Excel is fast an easy. Below I have a table of forename...
Get Comment Text from Cell Comments in Excel - UDF
Macro: This free Excel UDF outputs all text from a comment in Excel. This benefit of this UDF is ...



How to Install the Macro
  1. Select and copy the text from within the grey box above.

  2. Open the Microsoft Excel file in which you would like the Macro to function.

  3. Press "Alt + F11" - This will open the Visual Basic Editor - Works for all Excel Versions.  Or For other ways to get there, Click Here.

  4. On the new window that opens up, go to the left side where the vertical pane is located. Locate your Excel file; it will be called VBAProject (YOUR FILE'S NAME HERE) and click this.

  5. If the Macro goes in a Module, Click Here, otherwise continue to Step 8.

  6. If the Macro goes in the Workbook or ThisWorkbook, Click Here, otherwise continue to Step 8.

  7. If the Macro goes in the Worksheet Code, Click Here, otherwise continue to Step 8.

  8. Close the Microsoft Visual Basic Editor window and save the Excel file. When you close the Visual Basic Editor window, the regular Excel window will not close.

  9. You are now ready to run the macro.


Similar Content
Delete All Empty Rows or Blank Cells from a Range in Excel
Tutorial: How to quickly delete all empty cells or rows from a range in Excel.  This allows you to q...
Remove Gridlines from Only Part of a Worksheet in Excel
Tutorial: How to remove gridlines from a part of a worksheet in Excel instead of the entire workshe...
Print Only Specific Parts of a Worksheet in Excel
Tutorial: In Excel you can select parts of a worksheet to print while ignoring all of the other data...
Excel Forum