Many times we apply data filters based on data coming from email excel webpages etc by scrolling through the list of items to find and tick them and then apply the filters.
Suppose automation will be able to apply these filters by simply copying the data from the source and running this program in the second part of this video i'll demonstrate how.
To create and also deploy excel add-in so the code can be used in any workbook we'll create a ribbon custom menu and right-click menu as well.
To execute these utilities which you can replicate to add more other utilities in your accelerated before we begin if you haven't please do not forget to.
Subscribe and hit the bell icon for upcoming videos this is the dummy data that we'll be using for this uh for this video and for example let's say that i have to.
Where you want to apply the filter and just execute the code okay uh so this clipboard data could be anywhere it could be in a notepad it could be within excel itself you you.
Should be able to copy here itself and apply it within this column itself or you can simply you know like uh select the ones that you want to filter.
Copy apply filter okay so it should be that simple i've been using this utility for a while and i hope this will be useful for you guys as well.
Okay so first off um pause the data is being copied we will have to convert this data into array and then using that we will apply this.
Filter to the selected column first we'll write a function to convert the clipboard data to array to get the clipboard data we can use the reuse the same windows api.
That we use in our prior video
Okay so go to the blog vba a2z.blogspot.com then you should be able to find this working with clipboard api.Just simply copy the entire data okay and go to your visual basic editor module and simply paste this so here we just need to um use this uh this particular function.
Just get the clipboard data and then using this data we will convert it to array okay uh so first let's say that we have this data that we want to apply the filter.
Okay and uh once we have this copied uh the idea is that we'll copy this and then we'll select the the column where we want to apply the filter and run the program yeah.
And uh so let's call this new function called call this function convert data to array something like that and.
This will just return this variant so um first let's declare clip data as a string this data will hold whatever is there in the clipboard.
Okay now uh we can convert this clip data into um we can split this data let's call this lines and line our lines will be we'll split this.
The board data so we have discussed this in our other videos as well but basically you can split whatever is there in the data uh you know like in the string we can.
Split using delimiter so here the delimiter we'll be using will be a new line that will be character typing so if we have to demonstrate this in the.
Worksheet
Um you can just do equivalent is char function so if you put 13 here and you copy the cell which is a formula copy and paste it as.Values and if you press f2 you'll be able to see that there's a new line okay so basically we are trying to split this data set using a new line.
Okay so if i just uh f8 into this uh i think there's no data now let me first copy this if i run this code now f5 and see in the local window you'll be.
Able to see that it has split all these data lines the last one is playing here okay so now uh we'll need to clean this up and then we need to arrange this in.
Array okay so let's call this um total lines as long and then i s long that will be for array index and.
Then a rate so will be team let's call the stem array as variant so that it matches what it returns later.
Okay our total lines will be u-bound of our lines and then we'll put plus one just in case.
We want to it's possible sometimes we're simply copying it like this there's no new line at the bottom but if we are selecting the data like this then there.
Will be a new line so just like a placeholder or maybe we don't even need total lines we can arrange this on runtime.
Okay so now let's try and loop through all the lines for each line lines next line here we can have one more variable called.
Current value let's keep it consistent um this will be line and in case this line you know like uh for now we are just simply copying this.
Clean data it's possible you'll be copying the data from somewhere else and then it might have some strings uh which might not uh work well.
So it's better to always trim it or clean it so for that you can use the worksheet function to clean it and also you can further.
Trim it as well okay and here after we clean it if the value is not blank then here we can uh based on this we can.
Put this into our array here we can um first of all increment or i counter simply just incremented by one and then we'll redeem uh preserve the data.
Uh this will be one two i okay and then i will say that then data i is gonna be our current value okay so that will be pretty much.
It uh so we don't need this line and in the end we will return our temp array okay let me run this f5 okay there's no data so we can put a.
Handler later um so let me copy this knee fire f5 um .
Dude this should be the clearest area so you can see here uh you are still here and if you see on top here the the one at the bottom is blank but then.
Because we are on runtime identifying if there is new item and then based on that we are increasing our array index total you know like upper bound and then we.
Are assigning a value to the array okay so here we have the data that needs to be applied to filter okay uh for now this part will be pretty much.
Now we will write a function or yeah we'll just call this filter data using clipboard data .
So here we can um basically we will just get the clip filter data first oh no let's just declare range then range call this filter.
Range as range and then filter range we'll set this to active sheet dot um auto filter dot range now it's possible that the the table is.
Not filtered like in this scenario so we'll take tackle of that tank of that as well okay um now once we have this we also want to identify column that the.
User have selected it's possible that table is not always starting from a column right the filter could be um you know.
Starting from if this is uh if i select this it should be one and not three okay so we'll try to handle that let's call this dim current column s.
Long and then we also need a column to filter filter as long uh current column will be active cell active cell dot column.
And column the filter this can be you can try it out but there might be a better way to do this as well but this will be whatever is the active.