Ultimate Excel VBA Advanced Filter (And, Or, Find and Delete)

in this tutorial i'm going to pretty much cover everything related to the excel advanced filter and the excel vba advanced filter coming right up so what i'm going to do first is show you a completed excel vba advanced filter application go through how i do the queries and then a little later in this video i go through how i built it sounds good so what i've got here is a list of blog posts of roughly 45 or so in all pertaining to my business program or website and we're going to do some queries relating to vba so if i type vba into the page url part of the criteria and i click extract what we're going to do is get blog posts where the page url began with vba now what if i want blog posts where vba appears anyplace in the post well all i simply have to do is put an asterisk before vba and if i extract it now we now have a list of blog posts where vba appears anywhere in the data so for example vba is in the middle here it's in the end here and it's in the beginning here so what if you just want blog posts where vba is at the end of the row what we're going to do is again we're going to go up to our criteria which is page url i'm going to put in a single quote then i'm going to put in an equal sign then i'm going to put in an asterisk which is the wild card which says give me everything but and then i'm going to put in the criteria which is is in this instance vba so what i'm saying here is give me everything before vba but i don't want anything to be after vba and that's what the equal sign does which says basically i want exactly this nothing after the vba but you got to put a single quote before the equal sign otherwise it just won't work so press enter and i'm now going to do extract and here you can see you've got data where the criteria only exists at the end i can also do a filter in place and we'll get the same results so by filtering in place we've got our data filtered just to find those records i'm going to clear my data range by clicking my reset filter macro button i'm going to show you something now that isn't in the uh regular shall we say vba toolkit but you can absolutely do it check this out i'm going to say find record so i want to find the records where vba is at the end of the row so there's the first one in the data set there's the second one there's the third one there's the fourth one and notice the way i'm coding this the button changes to find next and now it's changed to find previous because there's nothing after that particular record i'll click it again it's found the previous cell the previous cell the previous row and it just keeps iterating like that and i can now click reset filter and bring everything back to normal i'm going to be showing you how to do this and this spreadsheet will be available for you to download let's just find something exact so i'm just going to put this into the criteria and i'm going to do find record this also isn't in the standard excel vba toolkit but you'll be able to do it with this delete matching row watch when i press this button that row is going to disappear from your data warning this will delete some of your data good to go that's the message we've put into the code yes bang the date is gone okay so let's look at uh setting up an advanced filter from first principles so here we've got some data now some things to note so first of all your data is going to need some headers so let's just put some of those in now it's got to have headers for the advanced filter to work so we just use those headers that i've copied in i am going to on the keyboard uh press my control and my asterisk key now this is the equivalent of select special current region so if you try that on your computer control asterisks click on us on a cell and control asterisk now this demonstrates a particular point the actual data that you want to be the database part of your advanced filter mustn't be touching anything else so for example in this instance we want to delete this rubbish here and we want to delete this so basically when you do a equivalent of control asterisks which is the same as pressing the f5 key clicking on special and clicking on current region you need to have this rectangle appear whenever you're working with an advanced filter or with data ranges they've got to be clean each column's got to have a header and the data range mustn't touch anything else so the next step i'm going to do is i'm going to insert some rows above the data range and i'm going to create a criteria range now the criteria range could literally just be like for example what we did earlier it could just be this or you could have a quite a criteria range that's the uh complete that's everything there because maybe you want to do queries on any one element of this so for now i'm just going to uh copy up the complete header but it can be just any one of the headers or any combination of them and we're going to create an extract range and as mentioned earlier the extract range can be a combination of anything so for example i can put year i can put year here as well and so we'll have year appear twice years ago i did that to create custom ports so i looped over data and i could get custom reports for the subsets of the data let's um do it via the menu now so i'm going to go to data advanced filter and i'm going to select a list range and the list range is going to be this and i'm going to to copy to another location i've got to give it a criteria range which in this instance is going to be this this of course is me doing it manually rather than encode and i'm going to copy it to this range here and if i go click ok we get all of the data now obviously this gets to be a little bit of a pain because you got to select the ranges each time but you know this is doing it without code so let's just uh put in vba again into here or let's say i want stuff ending with vba which is single quote equals asterisk vba so now i'm going to do advanced filter and it's remembered the list the way in fact it's remembered everything copied to another location hit ok so there we have our sub report which is showing page urls that end in vba that's the basics of the list now you will notice that the advanced filter area does not have functionality to find records or delete records like i showed you how to do earlier i've been using hidden functionality in the vba element of this because basically i've been using functionality that comes from excel 4 which was deprecated but it's still available under the hood don't build stuff that's mission critical for this but you know these commands have existed now for 30 years so it's unlikely that they're going to be taken away okay so now let's code up the situation i showed you earlier together okay that's done that let's go to the developer tab if you don't have the developer tab enabled on your spreadsheet you just go file options and click on customize ribbon and just make sure that developer is clicked okay so let's uh do some housekeeping first of all we're going to give our sheet a name so what we're going to be doing is we're going to be putting code behind this sheet that contains the data so i'm just going to alt tab it to the visual basic editor and i'm going to give the code name of the sheet just call it um i suppose just sheet data and i'm going to give the tab name just the name datasheet there we go and you can see the tab has changed there so now let's put some buttons on to the forum so we just go and i'm going to click on the develop the insert i'm going to use active activex not forms and let's just draw a little button and i'm just going to all tab again to the visual basic editor and drag up my properties window and i'm going to call this command extract and the caption will be extract so for speed i'm just going to resize this button a little bit go back to the tab and change the font size to make it a little bit bigger let's not say 14 let's say 18. okay that looks a bit better and i'm going to make it red and i'll go with bold so we now have our extract button and for speed i'm going to just copy the button and i'm going to paste it a few times so we've got extract so now i'm going to um move these buttons around and i'm going to put that one there that one there and we need one two three four five i need five buttons so if i put those five buttons together and now i'm going to click on this button and i'm going to change the name to filter in place so that's going to be command filter in place and i'm just going to change its caption to filter in place and i'm going to change this button to reset filter and it's caption to reset filter that's that one done and this one delete matching row okay that's those set up so now let's just create the click events for them so because i've named these buttons if you go to the drop down here we've got command extract and then we'll get what's the next one filter in place command filter and i want reset filter and i'm going to want uh delete matching row and i'm just going to put some stops in here okay so let's first of all do the simple extract what i'm going to do is create some range objects so you set range data equals now when you're in we are in the code sheet that's behind sheet1 so a quick way to reference the spreadsheet that your code is encapsulated within is to use a keyword called me so i type me dot range open back the sort of the microphone gets in the way of me seeing the keyboard uh me dot range let's say c7 that current region so that's range data central range x range crit equals me dot range now i want the criteria to be two rows so it's going to be two rows the first row for the headers and the second row for the items i'm going to put into the criteria now in addition to that the criteria rows can be more than one row if you want to put in multiple r style criterias so for example give me rows containing vba or give me rows containing python so what would happen is that instance the data would extract would show you a report containing both python and vba in fact i can in fact i can even demonstrate that to you so this piece is actually recorded out of sequence because i just remembered that i forgot to show you that so let's say for example we want to see something pertaining to let's say data hiding so if i type d-a-t-a well let's just type data and see what we get so if i do extract we've got the data hiding row but say i want to do a query which is based on data hiding and vba so i can type data here and i can type vba under it now what happens is the way i've programmed this is the criteria becomes this so what we're actually saying is give me data related to give me rows related to data and give me rows related to vba the actual um logic is called an all query because we're getting data or we're getting vba which means it'll give us records pertaining to data as well as records pertaining and additionally records pertaining to vba so if we extract this we've got you can see two rows with vba in them and one with data hiding in it so for example if i if i wanted to get a query whereby i want the record to contain both vba and data and in this instance i can say there's nothing like that what i would do is i would copy the page url to here so what we've got in column c i've got a criteria header of page url and in column d i've got a criteria header of page url now if i copy vba into there i'll just type it vba and i delete it here our criteria now looks like this so we're saying hey i need i need to have both vba and data in the page url column and in this instance we're going to get no records with that and there we go we don't have any records with that but if we look here we can see that we have does and vlookup in the same row so if i were to type vlookup and does here and do extract okay let's try it again so vlookup with an asterisk before it and does with an asterisk before it extract and there we have it so remember when we're dealing with the advanced filter the wild card is assumed to the right hand side of the criteria but to the left hand side you need to explicitly put in a wild card hope that helps so set range credit equals me dot range let's see i'm going to go c 1 dot current region now that will allow us to have more than one row just to demonstrate this particular aspect and that's the advantage of using vba for your advanced filter because you can just get things working at the press of a button now the extract range is different in so insofar as the extract range always only contains the header if you contain more than the header you will restrict the size of the extract that's going to come out of the data so for example if your extract range has three rows including the header you're only going to get two rows of data extracted now you could also put in range names which was for brevity i'm just putting in the addresses here so j7 now j7 i'm going to use that current region again so if i were to do j7 that current region just to show you this is what the current region would look like okay but we don't want that what we want is this so how do i change that back i do metered range j7 that current region dot resize one row okay so that's all of that set up and now all i have to do is range data dot advanced filter copy criteria range is range crit and extract range is range ext let's have a look and see does this work and let's of course take out the stop button so here we go i'm going to put in create but if i do extract on create let me just switch off design mode so it's now in run mode mode extract so that's the stuff that's got create in it so yeah i put create there for the page url so there's three records beginning with create so i wonder is there anything containing create so if i put a asterisk there and i extract yeah we got some more stuff with create so how to create okay so yeah the filter is working now let's do filter in place so if i go back to my vba editor so i just do filter here so what i'm just all i have to do in this instance is copy the code that's there so command filter is just filled up here so that's not so let's just do this oh yeah i need i need to put in another i need to put in another butt control c control v i'm just going to add i'm just going to get this button here reset filter command find and the caption here is going to be find record and i'm just going to add command find there so we put a stop there so we get that code as well it's the same code as the extract except instead of having filter copy i do filter in place and i don't need the range extract because we're not using it so i take that out but before i do any of that before i even run that i want to get the reset filter working so let's put in the reset filter commands now and it's simply me meaning the spreadsheet me dot show all data and that's that so let's just see where we're at so if i go back to the spreadsheet uh switch off design mode so i can click extract and we get everything because i've got no filter in there um some people usually say i've got no filter so filter in place let's do that again let's reset the filter uh filter in place reset filter extract there okay so we've got extract we got filter in place we've got reset filter now let's get to the magic of finding the records i'm going to stay in run mode now and i'm going to alt tab and i'm going to go to the find record one command find and here we go application dot execute excel for macro caps lock on data dot find now here's the thing for the xl4 macros to work what used to happen behind the scenes in excel was it would range name the data range as database it would range name the criteria range as criteria and it would range name the extract range as extract so for these excel 4 macro commands to work that has to happen in the code well it can happen manually if you range name these manually using range names you can get the same effect but we're going to do it in code for speed so here we go so then turn off my capstock range data as range range crit as range now so let's set it up again um i'm just going to um for speed copy in the code here now here's where we give it its names range data dot name equals database i'm going to leave it like this but i'm going to say if range crit dot rows dot count equals one then end if message box you need to enter a criteria and let's put in a carriage return and design vbcrlf ampersand dude okay and i can do vb information and give it a title okay so and then exits up because we don't have our code breaking do we and then arrange crit dot name equals criteria in quotes and with a capital c so basically what we got here is we're range naming the database and the criteria area with the range names database and criteria and then we execute the application of execute excel for macro data dot find command let's see if that works nice now the reason it was showing up in cyan in the other spreadsheet the spreadsheet that i'll actually give you as part of this download is i put in extra code to make this cyan but i just want to keep the basics here okay so find record that is working so now all we got to do is put in the code for delete matching row and that's going to be similar okay so now let's do the delete matching row part so let's just copy in our code again for range naming everything and applying the database name and the criteria name to the stuff and now application execute excel for macro data dot delete and these are actually xl4 macro commands but you know i mean really when i when excel when microsoft updated excel they kind of took away a lot of powerful features out of it i guess they were afraid users were going to sort of mess up their spreadsheets or whatever but there's some really powerful commands here i'm going to insert a record that i don't mind losing so i'm just going to click insert here and i'm just going to call it sean was here and this is programming and what we're going to do is we're going to delete business programmer so just to be sure what we want to do first of all is find record and there's business programmer okay and now i'm going to do delete matching row so if i were to highlight this in a particular color let's give it this blue and let's see if that gets deleted delete matching row bang gone doesn't this stuff excite you now remember for these excel 4 macro commands to work your criteria must be range named criteria your data range must be range named database and your extract range if you're doing some excel for macro stuff on extract must be range named you got it extract check this out one more thing i mean this might just make you giddy so i'm going to go to my immediate window and i'm going to do application dot execute excel for macro and i'm going to put in data dot form and i'm going to press enter which means you could put this in your macro code as well and let's see what we get oh my giddy cash we got us a data form so with the data form you can just go up and down see all your data find next find next find next i guess you this would be based on your criteria actually i don't know what this is doing to be quite honest when i do find next find previous it's just giving me pretty much everything at ah criteria let's click on criteria there we go you can click on criteria and now i can put into the page url business programmer in fact i'm just going to print prison business and see what i get and do i get anything i get b-u-s-i-n i don't understand that i really don't understand why that is giving me something i put in let me go to criteria again b-u-s-i-n so what has that got to do with in the page url i'm massively confused now let's just do vba and see what happens okay so it's given me that i guess if there's no cri if the criteria doesn't work i guess it gives you any everything let's just check this put in a load of rubbish find next find previous yeah so i guess there's some kind of bug in microsoft's code whereby if you put in some absolute rubbish into the form it'll just throw you something but if you put in something that makes sense like data hiding for because i can see data hiding here in page url so i'll type data h-i-d-i-n-g data hiding find next there we go find previous okay so that's a useful form and of course you've got new if you want to create a new record so i'm going to go close that so yeah didn't i tell you that this is probably going to be the best advanced filter tutorial out there i'm going to embed this in the blog post that's going to go along with this tutorial as well and there you can download the spreadsheet the link is going to be up there thanks for watching

Channel: Sean Johnson

Views: 1,027

Rating: 5 out of 5

Keywords: vba, excel advanced filter, advanced filter, excel vba, vba advanced filter, excel filter, database filter, advanced filter in excel, advanced filter and or, advanced filter and criteria, advanced filter multiple criteria vba, excel advanced filter multiple criteria, vba filter and or, advanced filter or criteria, advanced filter vba, advanced filters, excel advanced filter copy to another location, advanced filter excel, advanced filter examples, excel advanced filter copy

Id: SlTa5Utck0M

Length: 25min 45sec (1545 seconds)

Published: Sat Nov 28 2020