Using Excel VBA with advanced filter

At my workplace I often investigate a lot of different aspects of the data. One important moment is when I need to filter out and examine lots of different unique factors that corresponds to a specific event (I won’t go into detail regarding the actual real data due to secrecy in the workplace).

I noticed that my colleagues used Excels table and filter function and simply copy and pasted the results into a new sheet with the filtered data. This approach works but it is very time consuming and not optimal according to me, my reasoning for why this approach could be improved was the following ideas:

1) Time inefficient: The amount of time it takes to find the unique factor you want to filter out and then copy to a new sheet is very inefficient especially since we often need to do it around 20-30 times.

2) Not optimal for filtering: Using the filter functions are simple but not optimal because we want to filter in two columns but Excels filter function supports only “AND” filtering, but we need to use “OR” filtering because the factor will only be appearing in one of the columns in each observation.

3) Hard to organize: It became very irritating to always have to scroll around the large number of sheets without any proper way to navigate fast.

The above consequences made it obvious for me that a macro could be used in order to improve the above approach.

Creating the macro

The first thing I knew was that Excel is slow at filtering and copy a lot of data, so after doing some research I found out that advanced filtering was the fastest way to filter and copy data and it also works perfect with using the OR filtering option.

Let’s take a look at the sheet that contains the random data in this example that will be filtered:

The data in this example have 7 columns and 100 000 observations, which is a little less than the real-world data I use at work. The advanced filter technique is used by using the macro to paste the values in the selected cells that corresponds to the columns in the same sheet as the data. The advanced filter will then use the values and filter the data.

The example below shows how one number is automatically pasted into the filter criteria:

The excelbook contains two sheets, the first sheed named data is where the user paste the data, the second sheet which is named macro contains the macro that will filter the data.

The macro sheet looks like this:

The macro sheet has information that shows the user how to use the macro.

How the macro works

The goal of the macro is to filter many different numbers and create sheets for each number that exist in the data. The macro also creates hyperlinks to each sheet to make is easy and fast to navigate, each sheet with data also have a hyperlink that takes the user back to the macro sheet. I will now walk through the steps I used when creating the macro and how it works.

1 Input the numbers to search

The only thing the user must do is to copy and paste all the numbers they want filtered in column B in the macrosheet (which I hope is obvious).

2 Macro searches for duplicated items

The first thing that the macro does is to search the numbers to check if there are any duplications, this is due to two reasons: The first one is that two sheets can’t have the same name (which is important when the macro creates sheetnames, however, I would be able to fix this problem); The second reason is that it makes no sense to have duplicate since it would only create more sheets, take more time and become messier.

If there are any duplicated numbers than the macro will quit and show a message box that tells the user to use the “remove duplicates” function in Excel. I could have done this automatically in the macro but I wanted the user to have more free control.

3 Checks if number exist in the data

The second step is that the macro controls each number and checks if it exist in the data, if the number is exist than it is transported to a new column that collects all the numbers that are in the data, and the same happens when the numbers does not exist in the data but to a different column that shows all the non-existed numbers.

I did this step because I wanted the user to easily see how many numbers that existed but also to make it more easy to differentiate between the existing and non-existed numbers.

4 filter the data and create new sheets

The filtering is done by going one number at the time and pasting the value in the datasheet in order to use advance filtering. Then the macro creates a new sheet with the number as the name and paste the filtered data. Each new sheet also gets a hyperlink that takes the user back to the macrosheet.

5 creating hyperlinks to each new sheet

The last thing my macro does is to loop through all the numbers that exist in the data and make them into hyperlinks, so it becomes easy for the user to get to the wanted sheets fast.

The macro in action

Now that I have explained how the macro works lets have some fun and see it in action! Grab some popcorn and watch the gif below

The code for the macro can be seen below: