How to Clear Filters When Opening a Workbook in Excel
When creating a dashboard or your financial report in excel filter is a must. Filtering data is one of the best feature Excel can provide. But sometime clearing filters is a necessity to get the big picture. When we work on large dataset on excel, we use filter as we see fit. Also, after we are done working on the project, we save the file and close it. It’s a fact we don’t remember to clear filter. When we open the file next time the filter is still on and on big dataset it’s really hard to find which column is filtered. This is why we need a system that will clear filters each time we open a specific workbook.
In this advanced excel video tutorial, we'll guide you through the process of creating and implementing a simple VBA macro that clears all filters when you open a workbook. We'll also explain each step of the code to ensure you understand how it works and how you can customize it to suit your specific needs.
Understanding the Code
First, let's examine the VBA code that accomplishes the task of clearing filters when opening a workbook:
Here is the code for you to copy: / 103355882
Step-by-Step Explanation
Private Sub Workbook_Open(): This line declares the start of a subroutine named Workbook_Open. This subroutine will automatically execute when the workbook is opened.
Comments: The comments within the code are for explanatory purposes. They provide information about what the macro does and where to find additional resources.
Dim currentSheet As Worksheet: This line declares a variable named currentSheet of type Worksheet. This variable will be used to loop through each worksheet in the workbook.
For Each currentSheet In Worksheets: This line initiates a loop that iterates through each worksheet in the workbook.
If currentSheet.AutoFilterMode Then: This line checks if autofiltering is enabled on the current worksheet.
currentSheet.ShowAllData: If autofiltering is enabled, this line clears the filter and displays all data on the current worksheet.
Next currentSheet: This line indicates the end of the loop and moves to the next worksheet.
Implementation
Now, let's see how you can implement this macro in your Excel workbook:
1. Open Excel: Launch Excel and open the workbook for which you want to clear filters upon opening.
2. Open the Visual Basic Editor: Click on the developer tab and click on visual basic. If you don’t find developer tab on your ribbon please check this video to learn how to add developer tab in excel: • The Fastest Way to Add...
3. Open Code editor: Click on “This Workbook” From the “Project Explorer” from the left pannel.
4. Paste the Code: Copy the provided VBA code from here: / 103355882 and paste it into the module window.
5. Save the Workbook: Close the VBA editor and save the workbook as a macro-enabled file with the extension .xlsm
6. Test the Macro: Close the workbook and reopen it to test the macro. You should observe that all filters are cleared automatically upon opening the workbook.
Remember to save your workbook as a macro-enabled file to preserve the functionality of the VBA macro. Additionally, always exercise caution when using macros, especially if they involve automation of tasks or manipulation of data because there is no undo option when you use vba to do something in excel.
#Clear #Filter #ExcelVba
Thanks for watching.
----------------------------------------------------------------------------------------
Support the channel with as low as $5
/ excel10tutorial
----------------------------------------------------------------------------------------
Please subscribe to #excel10tutorial
goo.gl/uL8fqQ
Here goes the most recent video of the channel:
bit.ly/2UngIwS
Playlists:
Excel Tutorial for Beginners: goo.gl/UDrDcA
Intermediate Excel Tutorial: tinyurl.com/59a837py
Advance Excel Tutorial: goo.gl/ExYy7v
Excel Text Case Conversion Techniques: goo.gl/xiP3tv
Combine Workbook & Worksheets: bit.ly/2Tpf7DB
All About Comments in Excel: bit.ly/excelcomments
Excel VBA Programming Course: bit.ly/excelvbacourse
ChatGPT Excel Mastermind: tinyurl.com/46kn7tmd
Social media:
Facebook: / excel10tutorial
Twitter: / excel10tutorial
Blogger: excel10tutorial.blogspot.com
Tumblr: / excel10tutorial
Instagram: / excel_10_tutorial
Hubpages: hubpages.com/@excel10tutorial
Quora: bit.ly/3bxB8JG
Website: msexceltutorial.com/