Most of the other worksheets contain just a single excel table showing a list of the 10 highest grossing films for a particular year there's also one worksheet which.
Contains multiple tables just so we can demonstrate how to sort multiple tables on the same sheet the important thing about this example is that the ranges of data we're working.
With are formal excel tables you can see if i click into a cell in any one of these ranges but the table design tab appears in the ribbon here if i click away from one of those tables that tab.
Disappears now just in case you're not familiar with creating formal excel tables i've left one range here the 2017 top 10 films as a normal excel range if i want.
Control and t on the keyboard in the dialog box that appears it should pick out the range of cells for you but you can reselect this if you need to and you can also indicate whether your table.
Has headers mine definitely does so at that point if i click ok i've now converted that range into a table i've also saved this as a macro enabled workbook and then in the visual basic.
Editor inserted a module and created the basic outline of a subroutine for sorting our tables to begin let's look at how to sort a single table on the movies all worksheet.
I'd like to sort the 2020 films alphabetically by title to do that let's start by declaring a variable i'm going to call mine tbl and the type or the class is going to be.
A list object now i want to capture a reference to that specific table in that variable so i can say set tbl equals i need to begin by referencing the.
Posts Related:
- Best PowerPoint Infographic Template Free Download
- SWOT Analysis Powerpoint slide template - **Free Download***
- Este Microsoft Excel ! Sistema de painéis de estatÃsticas financeiras Tutorial 1.
Worksheet that the table belongs to so - ID Card Make
I'm going to use the code name here sheet2 and then refer to the list objects collection and in some round brackets.Place the index number of the table i want i'm going to use the number one that was the first table i created on that sheet if you preferred you can reference.
Tables by names as well if i head back to the excel workbook and just click into a cell in the 2020 list you can see on the table design tab the table name is shown up here.
You can change that name if you want to and of course you can just use that name in your code if i wanted to reference this by table 5 i could place the name at table five in double quotes inside.
Those round brackets like so for this example i think it's easier just to use the index number so i'll revert to the number one there now that we've referred to the table we.
Can begin working with the sort object which belongs to it and because we're going to do several things with the sort object let's use a with statement to help so i'm going to say with tbl.sort.
Provide a couple of blank lines and say end with and then inside that width block the first thing we'll do is clear any existing sorting which has been applied.
To the table so to do that we can refer to the sort fields property and then use the clear method to remove any existing sort fields.
Next i'd like to add a new sort field so i'm going to reference the sort fields property again and this time apply the add method then i'm going to type in a space after.
The word add to see the list of
Parameters and find there's a single compulsory parameter called key now this is a reference to the range of cells that the sorting will be based on.And for this table the sorting is going to be based on column b or the second column of the table or column b in the worksheet there are several ways i can specify that one fairly simple way is.
Just to refer to range b1 on this worksheet so i could set the key parameter to be equal to sheet2 dot range b1 now of course the obvious problem with.
That is that when we expand our example range b1 is not going to be the key for the second table or the third or the fourth a slightly easier way or at least a more.
Flexible way to reference the range of cells that i want to base the sorting on is to refer to the second column of the table object so to do this.
Let's get rid of the sheet2.range and instead refer to the table object and then refer to its range property which returns the range that comprises the entire table.
And then i can refer to the columns property of that range and refer to the second item of it so range columns 2. at this point all i then need to do is apply the sort to the table so i can say.
Dot apply and then if i run this subroutine we should find if we head back to the excel workbook that the first table on that worksheet has been sorted alphabetically.
By the title you can see that indicated there with a little symbol in the drop down list at the top of that column we can use some of the optional parameters to control the way this.
Sorting works so let's say for example we wanted to sort in reverse alphabetic order so let's head back to the visual basic editor and at the end of the add method.
After we've specified the key we can type in a comma and then specify the order parameter next i'm going to press control and spacebar and look for the excel sort.
Order enumeration and then type in a full stop after that to see the two options inside it so there's excel ascending and excel descending ascending happens to be the default so i.
Can say excel descending here and if i run the subroutine again we'll see that this time the results are sorted reverse alphabetically i think ascending worked better for this.
Particular example so let's just change this back to excel ascending the name of the enumeration is optional there we can just say excel ascending we don't need the qualifier there just to.
Make it a bit easier to read another possibly useful option is the sort on parameter so let's add another comma here and say sort on colon equals and then press control and spacebar and.
Look for excel sort on that's another enumeration and if i type in a full stop we'll see a list of the four possible options the default option here is sort on.
Values but we could also go based on the background color of the cell or the font color or the icon if there's one displayed values is certainly the most sensible one to go for for this.
Particular example so let's stick with that and again the name of the enumeration is actually optional there so we can just say excel sort on values one other quick option we can apply to.
The sort object itself rather than the sort fields is we can use the header property to determine whether or not the table has headers.
So obviously this table does and the the code is guessed correctly that there are header cells in that table but we can specify that explicitly by using the header property of the sort object.
So none of this will actually change the results here if i run the subroutine again we should see that same single table sorted alphabetically by the second column.
To apply the same sorting to all of the tables on the same worksheet is reasonably straightforward we just need to loop through all of the list objects on the same sheet.
So to do that let's head back to the visual basic editor and then rather than setting our table variable to be a reference to an explicit specific list object.
Let's change this so it says for each in sheet2 dot list objects so it's just a fairly standard for reach loop there we've used these many times.
In many many videos we'll need to make sure we then move on to the next list object we can say next tbl because we're referencing our key as the.
Second column of the table object that we're referencing then we don't need to change any of the other code we can just run that subroutine again and we should see now that all of the tables on the.
Same sheet have been sorted by the title column now to make the same thing happen on all the worksheets in the workbook we simply need to add another for each loop to.
Process all of the worksheets so let's head back to the visual basic editor and then let's declare a new variable so i'll say dim ws as worksheet then wrapped around this existing for.
Each loop we can say for each ws in this workbook dot worksheets we'll then add the next ws line down below next table so we'll say next ws and then just to satisfy my preferences.
