Table here and we have I've created basically multiple columns and we want to be able to sort in these columns by either name appointment date or address and I'll go ahead and hon hide row six.
But that'll come in handy a little bit later on okay so let's get started and the idea is that if a user enters a name here or a partial name we want it to filter by that name and then what we're.
Going to use we've basically separated appointment date into two columns so that we can not only separate a from date but a to date so that we can give it a range and as well as address okay.
Most of that work is going to be through VBA but I will go ahead and walk you through it right now and we also want to enter some sort of a text so that users know what they should be doing in these.
Fields so for example if we enter enter name here we want the user to know that they should enter a name here but we want that to look different when we see enter name we want it to look different.
Than they would if they entered to any ordinary text and we can do that through conditional formatting so if the words enter name renter here let's go ahead and add conditional formatting onto that.
So all we have to do is go into conditional formatting click new rule and then we can go ahead and click format cells based that contain and they sell by cell value would be.
Equal to inter name okay so we'll go ahead and set the format on that and let's format that a little bit different we can go italic and then we can set a color let's say to this medium gray.
Color so that this way when we set it your name it's italicized and like right but any other any other text is automatically black and regular okay and we want to.
Duplicate that for the others as well although we're going to change the text so we can do that simply with copy and paste we're gonna paste special and then we're gonna paste the format's okay and.
RELATED TOPICS:
We can do that again pay special we're
Doing it individually as opposed because it it'll provide a different conditional format to each one so we do want to keep it separate and paste each one.Separately as opposed to doing all of them at once and what that'll do is and we'll go ahead and reset the rows here just simply by clicking format and we'll go ahead and realign those since we've.
Set them all individually okay good and now what we're gonna do is we're gonna go ahead and update the conditional formats for appointment date now I want this to say from date and I want this to.
Say to date and this inter address so that when that text is entered it is also italicized and light gray so we'll go ahead and edit this one conditional formatting we're gonna manage rules.
Because we've already got a rule from our copy we've pasted this rule into this rule so all we have to do is actually update the rule now we have to update the text and we can change that.
From we're gonna change the to from date and we'll do the same to the other three so now we've got this from bit and because we've copied and pasted that we've copied and pasted the rule from.
This one as well so we'll go ahead and manage that rule and we're going to update the text two to date on this one as well and then we'll do that with the last one on the address as well so this.
Way we don't have to keep reentering the formats over and over again we just have to update the condition in which the formats are changed okay now that we've just about done our conditional.
Formatting we can go ahead and test it out and okay great let's go ahead and update that now that we have that set we can see to make sure it's all working all of it looks correct and when we.
Change it it's tested okay great all
Right now we're ready for the VBA portion alrighty from the Developer tab we'll wants to go into Visual Basic if you do.Not have this Developer tab visible simply go into the file and go into options and you'll go into the customize ribbon and then you'll see add-ins here and you'll excuse me you'll want to see.
Developer here you'll want to make sure that you go ahead and select that so that you can see it you can also press alt F 11 to make the visual basic window open so that we can start creating our.
Macro and the first thing you want to do is we want to create a module we can just right-click anywhere insert a module and we'll want to go ahead and name that module when you have a lot of.
Modules you want to name them independently so it's always good practice although in this case we'll just have one click on the properties window and then we'll go ahead and name.
That module it's called filtering module okay you want to make sure that the name that you give this filtering module is not the same as any macro that'll create a conflict so you want to keep those.
Names separate we'll go ahead and start entering a name for our macro and we'll call that sub and table filter okay and that'll give us the end sub and what we want to do now is we want to define some.
Of the names that we're going to be entering and we have basically four different fields okay to date fields and one name and one address so let's go ahead and dim those or dimension those.
We will do the contact name and the contact address both as string so we'll go ahead and do that now dim contact name and then comma contact address as string.
Okay because those are gonna be strings strings are just the same as text and then we have two date fields we have a to date and a from date so we're gonna go ahead and dimension those as dates.
Because we want to keep those as the date format so we'll do that and then from date as date okay one other one we want to do is we want to we want to filter this entire table we need to know.
The last row of the table as in our prior videos we've always defined the last row so we'll go ahead and dim the last row as long long needs a whole number okay so now that we've got that.
Set all the work we're gonna be doing is in sheet 1 so let's go ahead and make sure that we reference sheet 1 so they're gonna have to write it for each line so we'll go ahead and say with.
Sheet 1 okay now all of our macro all of the coding that we're gonna be doing will be within this statement within this width and end with okay and the first thing we want to do is we want to.
Set our last row what is the last row in this case we're going to use the column e okay and we want to say we don't want a filter we want to know the last row of the table that we're filtering so we.
Want to define that last row and then we'll go ahead and set it we can do that through VBA with just one line of code last row equals dot range remember we don't need the sheet number because.
We've already defined it here okay so we've already defined the sheet number one so we know which one we're talking about so we'll use E and then we'll just choose a large number this.
Would be the limit of our table in this case we'll just choose 99999 and then dot end and then Excel up that won't give us our last row okay so that line will set our last row in this case it's.
Going to be 21 but your table may have different rows so it's always nice in case you add rows it's always nice to define the last row and make it dynamic and variable so that you don't have to.
Worry up redoing your macro when you're adding data so that's always a nice although our data will stay the same and now we really pretty much we want to set our.
Fields up so basically we want to start our filtering and we have to define okay last name is going to be five right but one thing we don't want to do is is if this text says inter name we don't.
Want to filter it because that's just our default text so basically we want to create that filter for anything other than text inter name okay so we can go ahead and write that in a code okay so.
We can do that if dot range e5 dot value equals enter name okay then contact name remember we've defined it up that we defined it as a string and contact name equals empty okay so that it's dead that.
Means it's gonna we don't want to give it a value we want to make sure that we don't filter by the words inter name that's for it and then we can do else and if it's not so that means if it's.
Not inter name then then we can go ahead and define it in contact name does equal whatever is was is in the value of range b5 so when let's close that out so we get a little more space increase this so.
We can see dot range e5 dot value okay okay great now we can go ahead and just mark that as contact name and the next is going to be for the date so we'll do something a.
Little bit different here because if know from data is entered or no to data is entered we do want to set an earliest and a latest possible date so that will be helpful so what we'll go ahead and do.
Will say if dot range f5 dot value equals from date in that case if it's still from date that means no from date has been entered and then in that case we want to set the the fr date to equal.
Let's just say the earliest possible date that you could set this date to any day you want it but we'll go ahead and set it for January first let's say in 1900 this way any date dates before.