VBA - Filtering Data

It's a common mistake when we start using VBA to filter data by using long loops and conditions. This is probably the most logical way to interpret what we want to do. When we use loops and conditions we are taking to the code our natural process of filtering data. LOOK LINE BY LINE, AND IF A CONDITION IS MET, TAKE ACTION. Well yes, that works but it's not the optimal solution. Loops are great, I love them, but they are evil! They can slow down your process a lot when your data is considered big.

Fortunately, we have a better "not that naturally logical" way to do it. FILTER! Like water and oil you can separate values and once you have them visible, take the desired action. Let's see it on the code:





Ok, this wasn't that complicated. Let's see what we are doing here:
1- I'm selecting the Data that I want to filter.
2- I'm applying a filter to the 5th column of my Data, based on the Criteria "Bikes".
3- My Range shows now all those rows where "Bikes" is a parameter.
4- I'm selecting everything that's visible and deleting the Entire Row.
Here note the Offset(1) after rData. This is just to not consider the first Row where the headers are positioned.
5- I'm removing the Filter so sheet goes back to normal.


This way we avoid looping through 100K lines of rows when we only want to delete 50. See the difference? HOWEVER, not everything is sweet about this method;  what happens when your filter still shows 10K rows pending to delete? Yes, Excel takes 10 to 15 mins to delete the chunk of data. Why? Well here is why: Excel is very Tragic. Nah, not really, but since your data isn't organized (sort), Excel will have to delete the chunks of intercalated data and reorganized so it doesn't leave empty rows between them. If you are deleting 10K lines and these lines are all separated that means that excel will have to group the empty rows 10K times. Meaning more work. So, the solution:

Group all the Data you want to delete together and Excel will only have to group the empty rows once. Simple enough…. Look for the added code below.




Just a final advice: don't make rigid Procedures. What do I mean? This last procedure that I showed only works to delete rows in the Activesheet, 5th Column (E) and Bikes. Make it dynamical by passing these conditions as parameters in your procedure.


That's all folks... Cheers!

Comments

Post a Comment

Popular posts from this blog

VBA - Using JSON in VBA

Image
A very important skill you will need to know in VBA if you want to write professional applications is to handle JSON text and convert it into VBA objects. JSON is a very popular text-based file format used to transmit data objects. It's very common right now for APIs to return requests using JSON text. If you learn how to handle it, you can make some good use out of it. To put in an example (very trending right now by the way), imagine you have a Cryptocurrency portfolio you built in Excel where you store all your transactions and the number of Bitcoins you have. To know how much your Bitcoins are worth, you need to go and check the web for the current Bitcoin price. Or, instead, you can have Excel to automatically call an API to request this information. Parse out into an Object in VBA and display the price in the Worksheet to compare how much you Bitcoins are worth right now. Another example: let's say you want to know the list of the Best Sellers according to...
Read more

VBA - Copy Sheet from One Workbook to Another

Sometimes we need to insert a Sheet from one Workbook into another one. The solution is really easy. Follow bellow code: That was really simple. Just a few comments: Always make sure to define the workbooks that are going to be used (Source & Target). This will make the code more legible and it will make things easy to understand in the future. You should apply this rule everywhere in your coding experience. Variables are not only valuable when you have no other choice, they can be used to make your code clearer. That being said, note in the code how we are inserting the copied Sheet after the last Sheet in the Workbook Target. That's all folks... Cheers!
Read more

VBA - Change IF Statements for Reference Table

Image
If Statements are a crucial part of the daily VBA Development process. You can't live without conditional statements since they are, in core, the most fundamental decision derivative expression of programming. However, in Excel, where you have to deal with a lot of conditions, it might get tricky to use them. Imagine having 7 different conditions and for each condition 2 sub-conditions where 3 of them don't result in final "ELSE" condition. Hard to see, right? How many lines do you need to code to accomplish this? How about if in the future, you need to add more conditions, will you know where to place them? A lot of If Statements can be replaced for a table that drives all possible conditions. This will help with readability and will also help you to avoid modifying or adding more code in the future. Check this example where you have to return a fruit type based on the arguments passed to a function. (DO NOT PAY ATTENTION TO DESCRIPTIONS, THEY ARE VERY R...
Read more