The Secret To Creating Excel Tables With VBA + Free Cheat Sheet

while many of us have worked with tables in Excel what if we want to create a table with Excel VBA or what if we want to modify it or do things automatically with VBA and tables hi this is Randy with Excel for Freelancers and in today's quick training VBA for beginners we're going to do just that how do we work with tables in VBA in this training we're going to learn how to add update delete customize and change tables using VBA and this is really great if you have a lot of data and you quickly need to be able to create tables and we're going to be able to do that in just one click all right let's get started on this training please don't forget that I do create these VBA basic trainings each and every Saturday along with Comprehensive application development every single Tuesday make sure you are subscrib go ahead and click the like button and the notification button to make sure you get these trainings right away to you this template as like every week is Absol absolutely free all you need to do is look for the download word in the description below click the link into your name email and we'll get that sent over right away to you now I've got some sample data here if we want to create a table manually it's relatively easy to do but if we've got a lot of data we might want to automate it so how do we do it manually well we would click the insert button and click table here then we would select our range likewise I'll do contrl Z we could use contrl T as a shortcut set our range here and click okay and tables created but what if we want to do that with VBA it's very very easy we can also modify the table if we want a specific format we might want to do that we can do that all with VBA so how would we do that well we're going to get into the VBA Editor to start out and the best way to do that is use the developers or the shortcut alt f11 if you don't have the developers you can right click on any menu item here and you can click customize the ribbon and then just make sure you've selected the developer option down here that'll Ensure that the developer option will be displayed inside the developer you'll see the Visual Basic once inside the Visual Basic you'll have something like this and then what we can do is we can insert a module where we want to write our code so you can click insert and then module here here's where we're going to write our code so the first thing what we want to do is I'll bring this down we don't need to see this and I'll bring this up here all right very good let's create some additional data so I'm just going to copy this we're going to be using it so we can create different tables and then different situations very good the first one is let's go ahead and write the first macro to just simply take a static range meaning we have a fixed number of rows and a fixed number of columns to create a table so we're going to do something like sub create table and we need to di menion some variables in that so the first one is dimension the table as a list object that's what we're going to be working with also the range that we're going to be focusing on the range of the table so we're going to Dimension the range as a range we need to set that range what is the number of columns and the number of rows so we determine the starting point which is A1 and the ending point of that range which is going to be e16 so let's do that right now we're going to set the range is going to be equal to sheet one and I'm going to use the code name for Sheet one in case the user does change the sheet name we're going to work with the code name and the code name can be changed right in here so this is what we're going to be working with the code name which generally is not changed by the user so sheet one and then what we're going to do is the range what is the range that we're going to be working with A1 all the way through e and we're going to look at the last row and that's going to be 16 so that's going to be a static range so what we want to do that it's going to Define range for table once we have that now what we're going to do is we're going to set the table this is going to actually create the table so the table which we've already defined as a list object is equal to sheet one and then we're going to do list objects and what we want to do is we want to add a list objects and then we we want to do is we want to give it the source type is it external model query we're going to use the range which is the easiest what is the range that we're going to be using what is the source of that and it's range we've already defined it and whether we want to link The Source we're going to use EXL yes so we want to link that source and this is simply going to create the table that's all it does so we're going to run this macro I can use play or I can use F5 here and we see that we've created a table great so that works just fine however if we take a look in the table design we see it's just been given a random name but what we'd like to do is I'd like to give it a very specific name so let's try that I'm just going to delete what we've done here we're going to start again so we're just going to delete those columns and we're going to give it another try this time what I would like to do is I'd like to actually name the table and we can do that relatively easy with one additional line of code table. name is equal to and then we can just call it sales data great so we're going to put name the table all right so now what we're going to do is we're going to run this macro and we see that we've got it very good now that works just great that's great for a static table but what if we don't know the number of rows let's say we do understand the number of columns but we keep adding to the data we want to make it dynamic as far as the number of rows so how would we do that well let's go ahead and delete this here and then let's see maybe we're going to need one more just in case we want an additional data so that we can work with that so what I'm going to do is I'm just going to delete these here and then we're going to start back where we work now maybe we have a dynamic range we don't know the number of rows so what we're going to do is we're going to Dimension the last row as long and we need to determine what that last row is the last row is equal to sheet 1. range and we can use whatever column we want let's say we're going to use column A because we know column A is required a we use a large row large enough then what we want to do is use end XL up row and this is going to be the last used row of range so now instead of 16 it becomes Dynamic so we're going to remove the 16 we're going to put and last row so this way no matter how much data is in there it's going to accompany and we're going to run that code and we see that automatically it's already set to the last row very good so we see now how we can create a table and how can we use a dynamic but let's say we want to modify that table let's say we've got our table but now we want to modify it so I'm going to write another macro called sub mod ify table and inside this we're going to Dimension let's do the work sheet as a worksheet although it's not necessary if we want to di sometimes it's good so what a lot of people do is they'll Dimension the worksheet and then they'll say set worksheet as let's say something like uh this workbook do sheets and then we'll set sheet one so that's something we could do but it may not be necessary as opposed to just using sheet one all the time so it's up to you it's more of a preference let's keep it simple and Define as minimal variables as possible so we'll just do Dimension the table as we've done as a list object so not Callum object so once we have that what we want to do is we're going to set just as we did before but this time our table exists so set our table is going to be equal to sheet 1 Dot and then of course it's a list objects that we've already created on so list objects and what is it is called sales data and if you're not sure what tables we can take a look at the table we can look in the table design and we see that it's named sales data so we see it has been created so now we're referring to an already existing table so here setting variable for existing table once we do that we can then change the table style if we want how would that work we can do table dot then we have all the options for the table table style total rows range so we've got a lot of different things that we can use for this table we're going to Focus right now on the table style so we can do table style which is here and then what we want to do is going to be equals we can choose different table Styles how would you know what table style we can get some ideas inside the table design and if we hover over this we can see table style medium 1 table style medium 2 so we have these different table Styles here and that gives us some idea as we hover over we can see the intelligence of which one we might want to use so for now we will use something like table style medium four so we're going to write in here table style medium and then four so that's a good idea and we'll take a look at that so all we're doing is we're just setting the table and we can run this macro to take a look at what that looks like and we see it change to Green if we change it to five here we can just run the macro again and we can see what it changed to and we see that it's changed to this light blue great so we see how we can change the table style here change table style we can do more things we can add add additional columns if we want to so let's do that table referring to the table style this time we're going to use the list columns option what do we want to do with the columns we can count it we can create it we can create an item or parent but I simply want in this case to add a column and I want to give it a very specific name we can say total now if we don't do that it'll just add its own let's just take a look without let's just do add this is not going to change here the stock so we're going to run it and we can see it just adds it without any specific name but what if we want to add a specific name let's go ahead and delete that and we can give it a very specific name which is more helpful that name equals and then we'll give it a sub total or tax or whatever we want or let's do payment so here we can give it a specific name and we can run that and we see that it's been given a column of payment so we've added a column and we've named that column very good so what else we want to do with the table we can also delete a column if we want to so let's run this again let's keep the payment column we're going to add just an empty one without a name and then we'll go ahead and delete it so we're going to add it here simply adding the two columns so it added two columns and we see we actually have two more columns that got add them because payment already exists so it added an extra column and then another extra column so again this added one column with a no name because payment exists we can't have two columns with the same name and this added another column very good so let's go ahead and comment those out now let's delete the last two columns how would we do that so we can do table then what I want to do is I Want To Do List columns then we want to specify which column we want to delete so let's say we want to delete column two column two Dot and then delete so this will delete the individual column so delete column and what we're going to do is we're going to run that and what that's going to do is delete column two if we want to delete column one all we would need to do is change this to one and it's going to be deleted if we want to add rows to tables we can do just that we're going to use the resize so how would we do that so let's go ahead and comment this one out so we don't continue deleting those there's no more columns at that name now what we're going to do is I'm going to show you how you can add additional rows using the table resize property so we're going to do table do resize and right now it's going to ask us what is the range that needs to be resized I need to know the range so basically what I want to do is I want to add an additional row so we can do that using the table range property so the table range and then we're going to base it the resize is on the existing range so we're going to resize it and now it's going to base it on that table what is the row size so the row size is the number of rows plus one so dot range do rows how many rows are there do count so we're going to count the rows plus one so basically we're going to look at all the rows we're going to resize this table by the number of rows total rows plus one so this is simply going to add a new row we can add as many rows as we we want add new row so we can drop this down a little bit and then what we're going to do is we're going to run this macro here and we're going to see that it created a brand new row if we were to run it one more time it's going to add another row so we see it works up perfectly very good we can also extract information from a given table so how would we might do that so we're going to write a new sub routine here called sub access table properties and inside that we also need to Dimension the table as a list object and then what we want to once again we're going to set doing exactly what we did here we'll just set the table copy and paste that so we can refer to the same table now what we want to do is let's Rite a note accessing properties we want some information about that table and we'll put them in the debug print window so we can do debug we'll launch the immediate window in a moment debug print let's say I want to access the name so table. name I also want to get information about the totals so if there are any totals I want to know those totals so we can do debug do print and then table do show total so we can do that so if there's any totals for the table we can show that we haven't added in yet but we could certainly do that we also can list the rows count if we want to know how many rows are in the given table debug. print and we could do table. list rows. count so it's going to count number of rows in table and also what we want to do is we can do the body range we can access specific information about one of the items inside that so how do we do that inside the table debug do print and then maybe we want to do something with the table data so table and then we could do data body range is what I want to focus on and maybe I want to access let's say the first value of the second row so the row number and the First Column do value so this would be the First Column and then our second row value second row value going to put that in the immediate window so we've got a lot more to do so let's go ahead and view the immediate window so we can see exactly what's going to be going inside here okay we'll bring this up a little bit let's clear whatever's in here and then what we're going to do is we're going to run this and we're going to take a look so we've got the sales data here we have show totals is showing false cuz we're not doing that we're taking a look at the number of rows we see that we have here a total of 17 rows inside here so we've got 15 plus the two empty rows that's a total of 17 and then also we have the value which is here in the second row of the table here right here first row is two second row is here and the First Column so that's going to be displayed very good so it shows that we can access all the table properties we can also actually Loop through the table if we want to do something we can Loop through all the items in the table and happens very quick so how would we do that let's write a sub routine on that sub Loop through table and here we're going to Dimension the table again we're going to do exactly the same thing so we just copy and paste it up here as we want to refer to the table and maybe we want to run a loop through the table so to do that we can Dimension the cell as AR ranged cuz we're going to use a cell and now what we want to do is we want to Loop through table data so to do that we can use a for each Loop so for each cell in table. data body range that's what we're accessing it and then close our Loop next cell so what we want to do let's do something like debug print cell. Val so what going to Loop through all of the data within our table so we can run this macro if we bring it all the way up here row by row colum by col so we've got the first row we have the date the product the quantity so that's a kind of nice way of accessing looping through the data we can work with that very very easily very good this was a little fun quick trading where we learned how to create a table modify a table we learned how to change the look and feel of it we learned how to add rows add columns delete columns and we are also able to access the table properties and loop through all the data of course I will make this trading absolutely available through this template using the download you'll have this cheat sheet where you can look through that thank you very much don't forget if you want additional training I've got brand new updated trainings each and every week on my patreon platform or join our YouTube membership great to have you thanks again see you next week

Channel: Excel For Freelancers

Views: 7,386

Keywords: Excel VBA, VBA In Excel, Excel Application, Excel Application Development, Excel Software, VBA in Excel, Free VBA Training, Free Excel Training, Free Excel Course, Free Excel Training Course, excel vba tutorial, excel tutorial, tables with vba, excel vba, macro, excel macros, excel table, macros, tables vba, vba tables, table, tables, pivot tables for beginners, pivot tables excel, pivot tables

Id: PIlZ0SIooqU

Length: 16min 24sec (984 seconds)

Published: Sat May 25 2024