Remove Filters in an Excel Sheet using VBA


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.

Remove Filters V1.01.xlsb
Remove Filters V1.01.xlsb

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:
Advertisements

Fit Worksheet to Window in Excel


Have you ever felt Excel needed a Fit-Document-to-Window-Width feature like the millions of PDF Readers out there? Wouldn’t it be great if you could automatically fit your document to your current window size? The irony is, Excel already has all it needs to deliver that feature to you. Since spreadsheets come in various shapes and sizes, you might want to fit the width, or height, or the entire sheet to your screen. In this post, we will take a look at a routine that I wrote that you can use to fit any sheet to your window. And for you lazy bunch, I have an add-in that will let you call these macros from the Excel Ribbon.

Continue reading “Fit Worksheet to Window in Excel”

Duplicate a Sheet and retain a reference to it in Excel VBA


I recently worked on a project where my client had a Master template-sheet that needed to be duplicated and populated repeatedly. I needed to duplicate a sheet, and retain a reference to it, so I can rename it, reposition it, and pass it to another sub that populates it with data. I assumed there must be a very straight forward way to that, but was utterly disappointed when I found out there wasn’t.

Continue reading “Duplicate a Sheet and retain a reference to it in Excel VBA”

How to deal with the Numbers Stored As Text Error in Excel?


How many times have you encountered the “Numbers Stored as text” error in your data sets? It interferes with your LOOKUP and MATCH functions, and arithmetic calculations. Excel has a Convert to Number functionality to help with this situation, but it could be a lot better. You have to deal with your columns one at a time, sometimes one cell at a time. Also, I noticed that if the dataset is huge, excel takes a lot of time to push through; occasionally, it is so slow that you can see the cells getting updated one by one.

Continue reading “How to deal with the Numbers Stored As Text Error in Excel?”

Magically Select All The Used Areas In A Sheet using VBA


I recently published a post about automatically formatting a table in Excel using VBA. That got me thinking, how awesome it would be, if we could format all the tables in a sheet, with a single click. For that idea to work, we need to get all the used areas in a worksheet; and then use the Areas Collection to loop through the tables. We can access the Areas Collection through the Areas property of the Range object.

Continue reading “Magically Select All The Used Areas In A Sheet using VBA”

Magically Format Tables in Excel using VBA


Excel Tables make analyzing data, a breeze. It surprises me that it is not used as often as it should. It automatically “includes” new data you add to your spreadsheets, it automatically drags down formulas for you, it automatically formats the tables for you. In addition to that, you can use structured references that make your formulas tractable without having to name each range. You can also link an Excel Table to your PowerPivot Model. For a comprehensive, yet concise list of stuff excel tables can do, I recommend reading through this page.

Continue reading “Magically Format Tables in Excel using VBA”

Prepare for the show: A framework for hiding rows and columns in your spreadsheet applications

Prepare for the Show

Hiding a bunch of rows and columns in a sheet before showing it to your boss is inevitable.  I insert blank rows and columns around a table, so I can use the CurrentRegion property of the Range Object in my code. I add labels to all my named ranges in the sheet. I split out complex formulas into a couple of columns. Ultimately I end up with a lot of rows and columns to hide.  I desperately needed a framework to hide and un-hide rows and columns in all my sheets. I experimented with a lot of methods before settling down with one and I would definitely like to know if you have a better way to do it.

Continue reading “Prepare for the show: A framework for hiding rows and columns in your spreadsheet applications”

To Err is Excel, Handle your Errors with grace


Error handling is an important aspect of programming in VBA, especially if you are writing macros for other users. Unfortunately, many users ignore it completely. Visual Basics is an amazing programming language, but it lags far behind in the error handling department. All we have is the On Error”, “Goto” and the “Resume” statements. These statements allow only a few error handling structures, and each of the structures has its own set of expert proponents. In this post, I am going to share with you, a little block of code that I use to handle errors in all my spreadsheet applications; and hopefully offer a fresh perspective.

Continue reading “To Err is Excel, Handle your Errors with grace”

Progress Bar for all your excel Applications

Progress Bar

Excel is versatile by itself and VBA makes it even better by allowing us to do our own thing. Most of us use VBA to automate tasks of varying complexity – some macros are executed in a flash, but others take hours to run. While there are users who are happy with just a Msgbox “This thing is DONE!”, there are others who’d like to let the user know more about what is happening.

Continue reading “Progress Bar for all your excel Applications”