Has your data import procedure ever failed due to users applying filters on the data? This happens quite often, especially when you distribute multiple files, which you then stack. Copying filtered data copies only the filtered rows. You end up loosing the hidden rows if users filtered data before saving the workbook. Let us take a look at how to remove filters in an Excel sheet using VBA.
Before we dive into how to remove filters, let us briefly take a look at the types of filtering capability Excel offers. Filtering in Excel comes in two flavours:
Auto filter adds dropdown buttons to the columns, which offer you a variety of filtering and sorting options through an intuitive graphical interface. Advanced Filter on the other hand, lets you specify your filtering criteria in a range of cells.
Auto Filter is easy to spot visually; the dropdowns are hard to miss. However, Advanced Filter is a harder to spot. In both cases, if some of the Row Numbers to the left of a sheet appear blue instead of the regular black colour, filters have been applied on that sheet.
Mastering both these skills are essential for anybody who uses Excel at their workplace. There are a multitude of resources on the internet on these topics. I recommend you familiarise yourself with filtering data in Excel.
The ShowAllData method of the Worksheet object lets you remove any filters that were applied on the data. However, it throws a runtime error when filters have not been applied. One could simply supress the error and call it a day, but I’d loose sleep over resorting to such cowardice. Let us try to figure out a legitimate way of finding whether or not filters have been applied on a sheet.
The FilterMode property of the Worksheet object gets set to TRUE, when filters are applied either as Auto Filters or Advanced Filters. This is a Read-Only property. It can be used to check whether any filters are applied, before using the ShowAllData method. This will help us ensure a runtime error is not triggered when calling the ShowAllData method.
If you are interested in removing the Auto Filter Dropdown buttons as well, which is how I prefer it, you can use the AutoFilterMode property to turn it off. This property is not a Read-Only property, which means you can set it to TRUE to activate AutoFilters and vice versa.
Wrapping this all up, here is a Sub that I use very frequently, which you can use in your projects:
Sub RemoveFilters(ByRef WhichSheet As Worksheet) 'If data is filtered either using AutoFilters or Advanced Filters 'Show all the data If WhichSheet.FilterMode Then WhichSheet.ShowAllData 'Hide the AutoFilter DropDown Buttons If WhichSheet.AutoFilterMode Then WhichSheet.AutoFilterMode = False End Sub
Just pass a Worksheet to the above sub to remove filters without encountering a runtime error.
Note that having Excel Tables, also known List Objects in VBA, in the worksheet could throw these properties off, especially when the active cell is in a Table. If you are using Excel Tables, and I highly recommend that you do, you can deal with filters applied to a table specifically. We will reserve that discussion for another day.
Downloads
Click the link below to download a workbook with the above code. It also has a cover macro on removing filters in the active sheet.
Further Reading
- Advanced filters come in handy when you have to apply a rather complex filter criteria repeatedly. It is also easy to filter data using VBA if you create an appropriate criteria range. Coding the application of AutoFilters is rather cumbersome. Here is an introduction to using AutoFilters in Excel:
