VBA for Office Suite or Virtual Basic For Applications is becoming popular among Office professionals. It is now a powerful programming tool for users and the best thing about VBA is that you can use it even if you don’t know anything about coding. In VBA, we have macros, which are objects that we create for customizing certain functions. We then call these macros and run them for our own requirement.
Filtering is a very popular and commonly used tool in MS Excel. If you deal with very large data set in MS Excel, and you need to look at specific values or entries only, then you can filter the rows which are not of your requirement at that moment. For example, if you have created an Excel sheet containing marks of students in a class, and you want to find out who all scored 80% and more, then you can filter all rows where students scored less than 80%. So, this is the concept.
For VBA, it is possible to create Macros for filter operations. In this article, I shall show you how to do it.
Steps To Custom Filter Data For Excel Worksheet In VBA For MS Excel
First, you have to turn on the Developer Tab in MS Excel.
Next, tap on the Developer tab in the Ribbon. There are two types of references, one is the absolute reference and the other one is the relative reference. I have already written an article on the same topic using relative reference. But here, I shall show you without using relative reference. For sort and filter type of functions, always use Absolute Reference. Now, click on Record Macro .
Now, the Record Macro window opens. Select a shortcut key for you. For example, I have selected Ctrl+Shift+U. I have named the shortcut as FilterByCategoryClothingAccessories. This means the macro will show only the rows, where the category is either Clothing or Accessories.
Now, head over to the Data tab, click on the Filter tool. This will activate the Filter tool. Next, click on the dropdown arrow under Category column. Check only the clothing and accessories menu.
Now, go to the Developer tab and click on Stop Recording.
If you want to filter based on a number range, for example, here I want to see rows which have sales between $15,000 and $55,000. So, click on the drop down menu of Sales column, go to Number Filters and then select Between.
This opens the Custom Auto Filter window. Select the range. Click OK.
Your macro is now created. Next, go to the Developer tab and click on Stop Recording.
That’s all!
