Working with Tables in Microsoft Excel VBA

hello all and welcome back tables in excel is one of the most powerful yet under utilized feature and the reason why i say under utilized is because many people think that coloring cells creating borders around the cells makes it a table no that's not what a table is a table usually consists of a row header rows columns totals row and so many other things in fact it's the most efficient way to organize your data in a meaningful way it can store so many kinds of data for example employee data sales data appointments and so many other things it is also known as a list object in vba and that is because it has replaced the list functionality which was present way back in excel 2003 so in this video not only we will learn how to create a table but we will also learn how to interact with it resize a table insert rows columns and so many other things so let's begin i'm going to use this raw data to demonstrate how to work with tables if you notice i have two worksheets here both sheet contain the same data we will not work with the first sheet which is raw data we will work with the second sheet which is working sheet so if i go there you'll see that it contains the same data during this video we will try to convert this raw data to a table which should look something like this okay before we begin let's first visually understand the structure of a table this object is a table it is also referred to as list object in vba this is the range which is the whole area of the table this is the header row range it is the top row of the table containing the column headers this is the total range you may choose to display it if you want otherwise by default this is hidden it includes calculation that you want to perform using subtotal i would not put too much into subtotal in this video in fact i will cover subtotals much more in detail in the next video the section between the header row and the total row is called the data body range this is the list row so each row is a list row and this is a list column each column is a list column we will work with all these objects in details so let's launch the visual basic editor by pressing the shortcut key alt f11 if you're not sure what visual basic editor is then i recommend watching the video visual basic editor and introduction here i have code in few modules which we will use to work with the table in details let's open the first module you can either double click on this or you can right click on it and click on view code here i have a procedure called create table in this procedure we will create a table using list objects dot add method let's understand the syntax of list objects dot add the syntax is expression dot add source type source link source excel list object has headers destination table style name expression is a variable that represents a list objects object the list objects is a collection of all the list objects in a worksheet each list object object represents a table in a worksheet all the parameters be it the source type source link source and others are optional parameters the source tab indicates the kind of source for the query it can be one of the following excel list objects source type constants excel src external excel src model xl src query and so on the source depends on what you choose for source type when source type is excel src external then source is an array of string values specifying a connection to the source containing the following elements url list name and view guid when source type is equal to excel src query the source accepts odbc or oledb connection string when source type is xlsrc model the source accepts a workbook connection object when source type is xlsrc range then the source is a range object representing the data source and if you omit this the source will default to the range returned by list range detection code in this video we will work with excel src range as source type which will take the range from this workbook as source the link source indicates whether an external data source is to be linked to the list object object if source type is excel src external the default is true link source parameter cannot be used when the source type is xlsrc range excel this object has headers is an excel yes no guess constant which can have three values excel guess excel no or excel yes when excel list object has headers is excel guess then excel determines whether there is a header and where it is if there is one when excel list object has headers is excel no then the entire range is taken into account and excel will automatically generate headers when excel list object has headers is xlds then excel picks the first row as header the default value of excel list object has headers is excel yes destination is a range object specifying a single cell reference as the destination for the top left corner for the table if the range object refers to more than one cell an error is generated the destination argument must be specified when source type is set to excel src external when source type is set to excel src range the destination argument is ignored so feel free to specify it if required the destination range must be on the worksheet that contains the list object collection specified by expression new columns will be inserted as the destination to fit the new list therefore existing data will not be overwritten table style name is the name of a table style for example table style medium tube later in this video i will show you a neat trick on how to find the name of a particular table style for the time being while creating a table we will use table style medium two which is this type so let's go back and let's understand what the code is doing here i have declared variables for my worksheet last row and range here i have declared a variable for my table which is a list object and here i'm initializing the wsdata object to sheet2 let's go to sheet2 for a moment here i have a small data set let's say for the employees of a company called theme excel we have names email phone number date of fire etc we will convert this range into a table so let's go back here i'm finding the last row in column a finally i'm identifying the range that will be converted to a table here i'm creating a table using dot list objects dot add my source type is excel src range and i have set my source as the range rng i'm also telling vba that my data has header the table style that i'm using is table style medium one let's run this code and see if we get the desired result to run the code click on the run sub user form button or simply press the shortcut key f5 let's go to the worksheet the raw data is not converted to a table using table style medium one now you may not like this table style and may be curious as to which order table styles are available and if you could use them let me share a nice trick on how to decide and find the name of a particular table style click anywhere inside a table if any other cell outside the table is selected currently a1 is selected so that's okay when the selection is inside a table you will have access to a new tab called table designs so if i select outside the table that tab will not be visible to you so if you click anywhere inside the table you can see that tab reappear currently that tab is selected if that tab is not selected then click on that tab here you can see a section for table styles if you click on this drop down you can see the style has been divided in three sections light medium and dark and if you do not like any style then you can create your own by clicking on new table style let's say you like this one which is white table style medium one now how do i know what is the name of this style you can quickly find that by recording a macro if you are unsure how to record a macro then see the video how to record edit and run a macro in microsoft excel 365 for windows so let's click on the developer tab and click on record macro let's click on ok we will now head back to the table design and then i'll click on this drop down and then i'll select different styles so i'll select this first next i'm going to go back here i'm going to select this then this and let's say i select this i did this because we are recording and as i mentioned in the video how to record edit and run a macro in microsoft excel 365 for windows excel is recording my every action well almost every action so that is the reason why i clicked on several styles now let's stop the macro and let's go back to the vba editor and see what the vba code recorded for us [Music] here we see that we have the table style names of all the style that we clicked so now let's go and try a new style which is table style medium 2 so i'm going to copy this let's go to create table and i'm going to change this to table style medium too before i run this code what i'm going to do is i'm going to sheet 2 i'm going to delete all the data from here and then i'm going to copy this data and paste it again i'm going to do this couple of times manually later in the video i will show you how to convert your table back to its original state so now let's go back and let's run this code if you go back to the worksheet you will notice that that table has a new table style now earlier it was black and white now it's blue and white moving on to the next section which is converting a table back to range so let's go to module 2. here i'm initializing the my table object to the first list object in the worksheet since there is only one table i am using one if there were multiple tables then i would have used the table name to find the table name go to the worksheet and in the name box you can get the table name here you can also get the name of the table from the name manager in the formulas tab i've spoken about the name manager in the video named ranges in microsoft excel so if we click on name manager and if we click on this filter i will get couple of options you can use the table names to filter the named ranges so if you click on table names you will get all the names of the table in this list currently there is only one name so obviously it will not make any difference but in case there are a lot of names here you can actually filter the list on the table name so the name of my table is table one so i can actually go back and i can change this one to table 1. moving on to the next line of code here i am using the list object dot unlist method this method removes the list functionality from a list object object after you use this method the range of cells that made up the list will be a regular range of data so let's run this and let's see what happens if you look at the data this is no longer a table it's just a simple range however we see that the formatting is left intact so what we will have to do is we will have to remove the table style before we convert the table back to range so what i'm going to do is i'm going to repeat the process i'm going to delete this i'll go to raw data copy this data and i will paste it here again next i will go to module one and i will run this code and we have a table here notice the name of the table now is table two now let's go back to module two here i'm going to change the name of the table to table 2 and i'm going to uncomment this line what this line will do is it will remove the existing table style and then when the next line is run which is my table dot unless it will change the table back to a normal range with no formatting so let's run this if you go back to the worksheet you will notice that the table has been changed to a normal range with no table style okay moving on the next thing that we will cover is how to check if the worksheet has any table and we will also check if the worksheet has a table with a specific name so let's go to module 3. here i'm displaying the count of list objects in my worksheet this will show me the total count of tables so if the value is greater than 0 then it means that there is at least one table in the worksheet let's run this and i get 0 that is because the vba code was not able to find any table in this worksheet let's click on ok now i'm going to go and create a table let's go to the worksheet and we see that a new table has been created which is table 3. so if i go back to the code and now if i rerun that code let's see what do we get this time i get one so the vba code was able to find one table in this worksheet moving on let me comment this till now what we were doing was we were manually trying to find the name of the table now i will show you how to find the name of the table using vba to get the name of the table you can use the list objects dot name property this is a read write property that means you can use it to not only get the name of the table but you can also use this to name a table we will cover that in a short while so let's run this and i get the name as table 3 so i can use list object dot name property to get the name of the table let me comment this now moving on to the next section now let's check if there is a table with a specific name in the worksheet here i have a variable which has the name of the table that we want to check if it exists and here i have a function which will return true or false based on whether it was able to find the table or not if you're unsure how procedure and functions work then i highly recommend watching the video what are sub procedures and function procedures in microsoft excel for windows here i have a variable for my list object and here i am looping through all list objects in the worksheet and checking if any of the table matches the name that we passed to this function so let's run this and we get false that is because there is no table with the name employees now let's change the name of the table to table three because that is the name of our table yeah so this is table three so let's go back and i'm going to make this table 3. now if i run this it gives me true that is because it found a table with the name table three okay the next thing if you noticed here we are working with a for loop if you're not sure how loops work in excel then do watch the video understanding five loops in microsoft excel 365 for windows below this code you notice this uncommented code that is an alternate method to this loop to check if there is a table with that name so let's test it i'm going to comment the for loop and i'm going to uncomment this section here what i'm doing is i'm attempting to initialize the list object variable using the name that we want to check what will happen here is if a table with the name exists then this variable will get initialized but if there is no table with that name then this line will throw an error so to prevent or rather suppress that error what i'm doing is i'm sandwiching this line between on error resume next and on error go to zero so what this will do is if there is an error then it will suppress the error message and it will continue executing the rest of the code here i am checking if the variable was initialized if a table with the name exists then this variable will be initialized else it will be nothing okay so let's run this and i get true so this works so we saw three things here first is how to get the total tables count in the worksheet second how to get the name of the table and the third how to check if there is a table with a particular name moving on the next section that we will cover is how to name a table that we will create so let's run the code from module 2 to reset our data here i'll change the table 2 to table 3 because that's the name we have i've run this let's go back yes the table has been converted to a normal range now let's go to module four the syntax to add a name is expression dot name like i mentioned earlier it's a read write property so you can use this to assign a name to the table that you created let's understand what the code is doing here most of the code is the same code that we have in module one the only thing that we have here is this variable which has the name of the table that we want to create here i'm checking if there is already a table with the name employees and if there is then i am alerting the user and then exiting the process here i am naming the new table the rest of the code like i said is the same as in module one so let's run this if you go to the worksheet you will see that we have a table and the name of the table is employees now let's go back and let's try to run the same code again notice what happens when i run this code again it says there's already a name with employees choose a different name and the code exits gracefully that is because of this particular section of the code so let's try and change the name of the table what i will do is i'll make this employee data now notice what happens when i run this code i get a different error which is a table cannot overlap another table we are getting this error because there is already a table in that range and you cannot create another table on top of that table if you click on debug it will highlight this line so how do we handle this there are two options that we have first we alert the user and then exit the code gracefully currently the code is baking on this line so when we handle that it will not break on this line and like i mentioned it will exit the code gracefully second what we can do is we can use unlist on the table object which is using that range this way the table will be converted to a normal ridge and then we can create a new table on that range let's do one thing let's first stop this code and let's handle option 1 first i'm going to use option a procedure for this which is here let's understand what this code is doing here i have defined an extra variable for my list object here what i'm doing is i'm looping through every table and checking if the range of that table intersects with the range where we are trying to create a table and if it does then we are alerting the user and then exiting the code gracefully the rest of the code remains the same so let's run this and we get the relevant alert and the code will exit gracefully the moment i click on ok see it did not break on that line now let's handle option b where we will use unlist on the table object to convert it to a normal range and then we will create a new table i'm going to use option b procedure for this which is right here let's understand what the code is doing this code is the same as option a except instead of using employee data i'm using employees as there's already a table with that name so if you go back to the worksheet you will see that's the name of the table let's go back and here instead of showing a message and exiting the sub i'm unlisting it and then exiting the for loop so that the rest of the code can run now let's run this and let's see what happens and i get the message which is there is already a name with employees choose a different name so this section works now i'm going to remove the s and i'll make it employee so that this section is not executed and the rest of the code goes ahead now let's run this and this time i do not get any kind of message so if you go back to the worksheet you will notice that the new table has been created and the name of that table is employee moving on now that we have our table let's add some table style options for example totals row let's also highlight first column last column we will also try and tweak other properties like banded rows banded columns and other options let's go to module 5. the first property that we will cover is list object dot show totals property this property gets or sets of boolean to indicate whether the total row is visible here we are setting the show totals property to true when we run this code the totals row will appear right at the bottom of the table so if i go back to the worksheet and if i scroll down you'll notice there's nothing in row 22. when i run that code there will be a totals row here so let's go back let's run this code if you go back to the worksheet now you will see that you have a totals row in row 22 the totals row includes calculations you can choose what kind of calculation you would like to display in the table it can be sum minimum etc so if i click on this and if i click on this drop down you will see excel gives you options that you can choose you can display average you can display count you can display max standard deviation etc the next property that we will try to understand is list object dot show table style row stripes this property returns or sets if the row stripe table style is used for the specified list object currently if you look at the table it has banded rows if you wish you can remove this by setting this property to false so if you go back to the code i will comment this line and i'll uncomment this so if i set this to false and if i run this and if you go back to the worksheet you will notice that the table does not have any banded rows so let's set it back to true because it looks really nice with banded rows so i'll change this to true and i'm going to run this again so if you go back you'll see that the banded rows are back in the table just like you can enable banded rules you can also enable or disable banded columns the property for that is list object dot show table style column stripes this property returns or sets if the column stripe table style is used for the specified list object object currently the table doesn't have banded columns if you wish you can apply this by setting the property to true just for testing purpose let's set it to true and then we will set it to false because i really don't like banded columns in my table so what i'm going to do is i'm going to comment these two lines and i'm going to uncomment this and i'll change this to true let's run this if you go to the worksheet you will see that the table now has banded columns it looks pretty weird this is the reason why i usually keep it off so i'll go back i'll change this to false and then i'm going to run it again and the banded columns have disappeared the next property that we will tweak is list object dot show headers this property returns or sets if the header information should be displayed for the specified list object object so if you set this property to false it will hide the headers currently headers are displayed since we want the headers we will not set this property to false but just to demonstrate let's do that and then we will reverse the changes so i'm going to comment this and i'm going to uncomment the next line and i'll change this to false if i run this you'll notice that the headers have disappeared obviously we want headers so i'm going to set it to true again and let's run this and there you go we have our headers back if you notice our headers have filters so that's the next property that we will tweak by default the headers will have filters if you do not want to show the filters then you can set it to false by setting the property list object dot show auto filter drop down to false let's test it i'll comment this and i'm going to uncomment the next line i'll make this false i'm going to run this if you go back now you will notice that the headers do not have any filters i usually like keeping my fitters because i like my clients to be able to filter on different values so let's go back i'm going to change this to true [Music] and we have our filters back the next property that we will tweak is list object dot show table style first column this returns or sets if the first column is formatted for the specified list object object so we will first set it to true and let's see what happens so this is currently how my table looks like the first column look at the font it's not bold okay so now what i'll do is i'll go back to the code i'll comment this and i'll uncomment this now if i run this see what happened to the first column the font is bold you can achieve the same thing with the last column using the list object dot show table style last column property this property returns or sets if the last column is displayed for the specified list object object look at the last column now let's go back and we will uncomment this let's run this there you go even the last column now has bold fonts all these properties that we treat are the same properties that you can access in the table style options group in the table design so if you go here these are the options that we just tweaked now let's try and read different values from the table as i mentioned earlier this is the header row range this is the total slow range this is the data body range this is the list row and this is a list column so let's first interact with the header row to work with a header row we can use the list object dot header row range property this property returns a range object that represents the range of the header row for a list let's go to module 6. here i've initialized my table to existing table employees here i have declared a range which will hold my header row range and here i am initializing it here i'm getting the address of that range so that we can visually see the address of the header row range and in this section i am accessing the individual headers by simply using header row parenthesis followed by the column number so here the code will return the value from the seventh column in that range let's run this the address that i get is a1 to j1 so my header row range is from a1 to j1 if i click on this i get designation which is the value of the seventh column let's click on ok similarly if i want to let's say get the value from the third column all i have to do is change this to three and if i run this now i get the address okay and i get the value last name from the third column just like we worked with the header row we can also work with the totals row let's check out the procedure accessing total row to work with the total row we can use the list object dot totals row range property this returns a range object representing the total row if any from a specified list object object so this code is similar to what we had above here i have declared a variable rng totals row as range and i'm initializing it to the totals flow range here i'm displaying the address and then i'm trying to pick up the value from the tenth column in the totals row so let's run this this time i get a 22 to j22 which is this row if i click on ok see what do i get i get 2.09 which is 209 let's click on ok the next property that we will cover is list object dot data body range this property returns a range object that represents the range of values excluding the header row and the totals row in a table to access individual values simply use data body range parenthesis followed by the row number and the column number so let's check out the procedure accessing data body range which is right here this procedure is also similar to the above procedures here i have a variable rng data body and i'm initializing it to my table dot data body range here i'm trying to get the address of that range and finally i'm trying to extract the value from that range from row five and column two so let's run this i get a2 to j21 which is this range minus the header row and the totals row when i click on ok i get brenda this is from the fifth row second column let's go back the next property that we will cover is list object dot list rows this property returns a list rows object that represents all the rows of data in the list object object to access individual values simply use list rows parenthesis followed by the column number let's check out the procedure accessing list row range here i have the rng list row variable which i am initializing to the fifth row range here i am displaying the address and then i'm trying to extract the value from the first column in that row let's run this and we get a6 to j6 which is the fifth row in the data body range and when i click on ok i should get one double 0 0 5 which is the value from the first column of that row see let's go back just like we used list object dot list rows to access the rows you can use the list object dot list columns property this property returns a list columns collection that represents all the columns in the list object object let's check out the procedure accessing list column range which is right at the bottom let me add few blank lines so this comes right at the top [Music] it'll be easier to read the code here you can either use the column number or the column name okay an interesting thing if you use dot range with list columns then you will refer to the entire column including the header row and the total column and if you want to work with the range excluding the header row and totals row then use dot data body range let's test both of them so first let's test the data body range here i'm displaying the address and here i'm picking the first value in that column so let's run this i get a due to a21 so this excludes the header and the totals row let's click on ok and i get the first value which is one zero zero zero zero one now let's comment this and let's uncomment this when i run this code it includes the header and the total stroke now when i click on ok instead of showing me one double 001 it will show me employee id see so remember two things when you're working with the columns you can either pass the column number or you can pass the column name second if you want to work with the range which does not include the header and the totals row then use dot data body range and in case you want to include the header and the totals row then simply use dot range now let's try and add some rows and columns to the table let's view the code in module 7. we will work with the rows first to add a row we have to use listrows.add method the syntax is dot add position always insert expression is a variable that represents a list rows object position is optional it specifies the relative position of the new row always insert is also optional it's a boolean value that specifies whether to always shift data in cells below the last row of the table when the new row is inserted regardless if the row below the table is empty if set to true the cells below the table will be shifted down one row and if set to false if the row below the table is empty the table will expand to occupy that row without shifting cells below it but if the row below the table contains data those cells will be shifted down when the new row is inserted let's check out the first procedure adding deleting rows to table here i have declared the variables and initialized my table and here i'm adding a row since i'm not specifying any row position the row will be added at the end so let's run this and let's check it if you go to the worksheet you will see that a row has been inserted in row 19 of the worksheet which is just above the totals row now if i want to add the row at let's say the fourth position then we can also do that so if you go back to the code i'm going to comment this and i'm going to uncomment this here i have mentioned the position as four so when i run this code one row will be added at the fourth position so if you go back you will see a row has been added at the fourth position let's go back and let's comment this now let's learn how to delete a row deleting a row is as easy as you add it to delete a row simply use the list row.delete method this method deletes the cells of the list row and shifts upward any remaining cells below the deleted row the syntax is expression dot delete let's delete the row we just added at the fourth position so what i will do is i will uncomment this line so i'm going to run this and if you go back you'll notice the row which was here is gone so we saw how we can delete one row now let's try and delete multiple rows to delete multiple rows we have to use a range dot rows delete method simply mention the range that you want to delete so let's go back and this time i'm going to uncomment the last line here i am specifying that rows 6 to 8 should be deleted if i run this code then these lines will be deleted let's go back and let's check so let's run this and the vba code deleted those lines so let's go back just like you can add rows you can also add columns to the table by using list columns.add method the syntax says expression.add position expression is a variable that represents a list columns object position is an optional variable it specifies the relative position of the new column that starts at 1. the previous column at this position is shifted outward if position is not specified a new rightmost column is added and a name is automatically assigned to it the name of the new column can be changed after the column is added so let's check out the procedure adding deleting columns to table here i have declared the variables and initialized the table here i am adding a column just like i added rows above so i am going to run this you go to the worksheet you will notice that one column has been added at the end and if i expand this the vba code named it as column one let's go back just like we added a column at the end we can also add a column at a specific position so if i comment this code and i uncomment this line so here i am trying to add a column at the fourth position let's run this and a column has been added let me make this a small column this happened because it copied the formatting from column e and column e was already this big okay let's go back now let's see how we can delete a column deleting columns is similar to deleting the rows you can specify a position or you can also mention the name of the column the syntax is expression dot delete expression represents a list column object this method deletes the column of data in the list so i'm going to comment this let's try and delete the first column so if you go back the first column is employee id this column will be deleted when we run this code see it's gone now let's try and delete a column by giving the column name here the column name is first name last name column two email etc let's try and delete the first name column so i'm going to comment this line and i'm going to uncomment this line when i run this that column is also deleted so remember when you're deleting columns again you can specify either a column number or a column name okay moving on now let's see how we can sort a table but first let's copy the data from sheet1 and let's recreate the table so i'll delete this i'll go to the raw data i'll copy the data and i'll paste it here again next i'll go to module 4 and i will recreate the table let's go back yes the table has been created great now let's move on to module 8. here i have declared the variables and initialized my table and here i am using the table.sort method which will sort the table here i am using sort fields dot clear method which clears all the sort fields object if there are any and here i am using the sort fields dot add method to create a new sort field which will return a sort fields object the syntax of sort fields method is expression.add key sort on order custom order data option key is the required range it specifies the key value for the sort short on is optional it is the field to sort on the order is also optional it specifies the sort order custom order is optional it specifies if a custom sort order should be used and data option which is also optional specifies the data option so in this code i have specified the key as first name range i'm sorting on values and here i'm specifying that the sorting has to be in ascending order and here i am specifying the data option property as excel sort normal which means we want to sort numeric and text data separately this is the default value of this property you can ignore mentioning this if you want the other value this property can have is excel sort text as numbers which means that we want to treat text as numeric data for the sort and here i'm telling vba that my data has headers finally i'm applying the sort so let's go ahead and let's run this if we go to the worksheet we see that the first name column has been sorted in an ascending order so here we sorted on one column what if we wanted to sort multiple columns so let's go back and check out the second procedure which is here all you have to do for this to work is to create a new sort fields object using sort fields dot add as shown here here i am now sorting on the second field as well which is last name now when you run this you may or may not see the difference so to see a clear difference let's change allen to allen so i'm going to copy this and i'm going to paste it here the reason why i did this is because the last name of alan is cooper and for this and it is alan so when i sorted on the second level which is on last name then aaron allen should go above alan cooper okay let's test this now i'll run this code again and if you go back see how alan google moved below alan allen so this is how you can sort the column or columns in a table you can specify one level or you can specify multiple levels for sorting the data moving on to the last topic now let's see how we can resize the table you can shrink or expand the table by using the list object dot resize method this method allows a list object object to be resized over a new range no cells are inserted or moved the syntax is expression dot resize range expression returns a list object object the range is a required parameter which is the new range so let's go to module 9 to understand how this works so this part remains unchanged here i'm trying to shrink the tables range currently the table is from a1 to j21 let's shrink it to let's say a1 to j15 so let's go back and let's run this code if you go back to the worksheet you will see that the table range is now from a1 to j15 and the rest of the six rows are no longer a part of this table just like you shrank the table you can also expand the table using the same method so i'm going to go back to the code i will comment this and i will uncomment this so here what i'm doing is i'm expanding the row back to its original range which is a1 to j21 so when i run this now you'll see that the table includes the other six rows as well if you go back to the code you will see that i'm using hard-coded ranges if you wish you can also pass a dynamic range to list object or resize method so let's comment this and i'm going to uncomment these three lines here what i'm doing is i'm using the range.resize property to create a dynamic range here i have declared a rng variable for my range object and here what i'm doing is i'm using the range.resize property to create a dynamic range this property resizes the current specified range and then returns a range object that represents the resized range the syntax is expression.resize row size comma column size the row size is an optional parameter it is the number of rows in the new range if this argument is omitted the number of rows in the range remains the same column size is also optional it is the number of columns in the new range if this argument is augmented the number of columns in the range remains the same so here what i'm doing is i'm creating a dynamic range which has the same range of the table range plus ten extra rows and five extra columns and then i'm passing this dynamic range to the list object.resize method so let's run this and let's see what happens if you go to the worksheet you will notice that now the table has 10 extra rows and if you go towards the right it has five different extra columns so if i autofit this you'll see these are five columns working with tables is super easy and it is really fun if you have a good understanding of the list object time for a quick recap in this video not only did we learn how to create tables but we also learned how to interact with different components of the table whether it was the header row range or the rows the columns the totals row and so many other things we learned how to add columns or rules at the end of the table we also learned how to resize the table how to check if a worksheet has a table or whether a worksheet has a table with a specific name and so many other things so i hope this video was easy to understand but if you still have any questions please feel free to leave a comment in the comment section below or you can also email me on support teamexcel.com i also have a facebook page the link is in the description below feel free to join that if you have any questions please post it there i'll be more than happy to answer your queries if you would like to extend support towards this channel if you think that i am putting some good content then go ahead watch couple of videos drop couple of likes and if you are really serious about learning visual basic programming from scratch then go ahead and watch the playlist because that is where i have kept the videos in a specific sequence which will really help you learn visual basic programming from scratch and if you still haven't subscribed to this channel then go ahead and subscribe to this channel by clicking on the bell icon and then in the next video i'll talk to you about one more powerful yet under utilized feature of microsoft excel which is subtotals

Channel: Siddharth Rout

Views: 7,317

Keywords: Visual Basic For Application, Visual Basic for Applications Editor, Microsoft Excel, Excel VBA, Excel-VBA, ExcelVBA, VBA, Excel, VBAEXCEL, VBE, VBAE, Macros, Table, ListObjects.Add, TableStyle, ListObject.Unlist, ListObject.Name, ListObject.ShowTotals, ListObject.ShowTableStyleRowStripes, ListObject.ShowTableStyleColumnStripes, ListObject.ShowHeaders, ListObject.HeaderRowRange, ListObject.TotalsRowRange, ListObject.DataBodyRange, ListObject.ListRows, ListObject.ListColumns, Siddharth Rout, TameExcel

Id: RVLwhVr22y8

Length: 54min 11sec (3251 seconds)

Published: Mon Jul 18 2022