While many of us have worked with tables in Excel what if we wanted to create a table file with Excel VBA or what, if we wanted to modify it or do things automatically with VBA and tables Hey, this is Randy with Excel for freelancers and in today’s Quick VBA training for beginners and we’re going to do this. How We can do that working with tables in VBA In this instructible, we will learn how to add, update, delete, allocate and change tables using VBA, and this is really great. If you have a lot of data and you need to quickly be able to create tables – and we are going to be able to do So with just one click – Alright, let’s get started with this training. Please don’t forget that I created these basic VBA training exercises for each Saturday with Comprehensive Application, Development Every Tuesday Be sure to subscribe ahead and click the like button and the notification button to make sure you get these trainings right away.
This template is for you as every week. Absol is completely free. All you have to do is search for the word download in the description below click on the link in your email name, and we will immediately send it to you now. I have some sample data here. If we want to create a table file Manually, it’s relatively easy to do it. We do it, but if we have a lot of data then we might want to automate it. So how do we do it? Do it manually? Okay and we’re going to click the insert button and click the table here. Next, we’re going to select our group. Similarly, I’m going to do control Z. We can Use control T as a Select the shortcut, our range here and click. Okay and the tables are created, but what if we do that, I want to do it using VBA, it’s very difficult. It’s also easy to modify the table. If we want to.
We want a specific format we might want to do that. We can do All using VBA how well we can do that. We’re going to go into the VBA editor to get started and the best way to do that is to use developers or the alt f11 shortcut. If you don’t have developers, you can right click on any menu item here and you can Clicking on the Customize ribbon and then just make sure that the Developer option is selected here will make sure that the Developer option will be displayed within the Developer. You have to see Visual Basic once inside the Visual Basic file. You will have something like this and then what we can do is we can enter module where we want to write our code. So, you can click insert and then module here here where we’re going to write our code. So, the first thing we want to do is I’m going to bring this down. We don’t need to see this and I’m going to bring this up here very well.
Okay, let’s create some additional data, so I’m going to copy this we’re going to use it, so we can create different tables and then different, very good cases. The first one is: let’s go ahead and write the first macro just to take a fixed range, which means we have a fixed number of rows and a fixed number of columns to create table. So, we’re going to do something like create a sub tablé and we need to di mention some variables in that. First are the dimensions of the table as a list object. That’s what we’re going to work with the range that we’re going to focus on the range of the table, so we on our way to the range dimension as a range we need to set that range. What is the number of columns and the number of rows? So, we set the starting point, which is A1 and the end point of this range, which is going to be e16. So let’s do that now going to set the range is going to be equal to the first sheet and I’m going to use the code name for the first sheet.
In case the user changes the name of the sheet, we are going to work with the code name and the code name can be changed here. So, this is what we will be working with the code name, which is generally not changed by the user. So, one sheet and then what we are going to do is scope. What’s the range we’re going to be working with A1, all the way through the post and we’re going to look at the last row, and this is going to happen you’re 16. So this is going to be something fixed range, so what we want to do is go to define the range for the table once we do that We have this now. What we’re going to do is we’re going to set this table actually going to create the table that table that we’ve already selected the list object is equal to the first sheet and then we’re going to make a list of objects, and what we want to do Is we want to add a list of objects, and then we want to do? Are we want to give it the source type? This is the external form query that we’re going to do use.
The range which is the easiest is the range that we’re going to be using what is the source of that and it’s the range that we’ve already defined and whether we want to link to the source we’re going to use EXL yes, so we want to link To this source – and this will simply 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 see – we’ve created a nice table, so it works fine. But if we take the file look at the design of the table, we see I’ve just given a random name, but what would we like to do with it is? I would like to give it a very specific name, so let’s try to be so we’ll just delete what we did here, we’ll start over, so we’re just going to delete those columns and we’re going to give it another try. This time I would like to do is what I want to name the table Actually, and we can do this relatively easy, with one extra line of code table
Name equals and then we can just call it sales data great, so we’re also going to put the table name all right in it. Now. What we’re going to do is we’re going to run this macro, and we see that we’ve got it pretty good. Now, just great that’s great for For a static table, but what, if we don’t know the number of rows suppose we do understand the number of columns, but we keep adding to the data. We want to prepare it’s as dynamic as the number of rows. So how do we do that? Well, let’s go ahead and delete this here and then, let’s see, maybe we’ll need one more Just in case we want additional data, so we can work with that. What I’m going to do is I’m just going to go, delete this here and then we’re going to start over where we’re working now, maybe we have a dynamic range, we don’t know how many rows. So, what we’re going to do is we’re going to go to the row dimension.
The last is long and we need to define what is this last row is. The last row is equal to sheet 1 Range, and we can use any column we want. Let’s say we are going to use column A because we know that column A is required, and we use it. Big row is big enough. Then.
What we want is what you need Doing is using the end of row XL, and this is going to be the last used row of the range and now, instead of 16, it becomes dynamic. So, we’re going to remove the 16 we’re going to put and the last row so that way, no matter how much data there is going to accompany we’re going to turn on This code, and we see that automatically. It is already set to the last row. Very well so now we see how we can create a table and how we can use dynamic. But let’s say we want to modify. Let’s say we have our table, but now we want to modify it. So, I’m going to write another macro called sub modify table and inside this We’re going to dimension.
Let’s do this worksheet as a worksheet, even though it’s not necessary. If we want to do that, sometimes it’s fine. What a lot of people do is they’ll. Do this measure the worksheet and then they’ll say set the worksheet as Let’s say something like uh, this workbook do the sheets and then we’ll assign the first sheet. So that’s something we can do that, but it may not be necessary instead of just using the first sheet time. So it’s up to you, it’s more of a preference. Let’s make it simple and we define it as minimal variables as possible, so we’re just going to scale the table like we did.
It’s done as a list object, so it’s not a Callum object so once we have that what we want, what we need to do is we’re going to put it exactly as we did before, but this time our table exists So set our table. Equals 1 point sheet and then of course it’s the list of objects that we have already created on that list of objects and what it is called sales data and if you’re not sure about it tables, we can look at the table. We can look at the design of the table and we see that it’s called data Sales, So we see it’s been created so we’re now referring to a table that already exists. So here we define a variable for the existing table once we do that, we can then change the table style. If we want how this work we can do on the table, then we have all the options for the table. Table style range total rows. So, we have a lot of different things that we can use for this table, we’re going to focus now on the table style.
So, we can do the table style, that’s here and then what we want to do is going to be. Even we can choose different table styles. How do you know what that is Table style that we can do get some ideas inside the table design and if we hover over this, we can see table style. Average 1 table style average 2. So we have these different table styles here, which gives us some idea as we hover over, we can see the intelligence that might We want it to use that now we’re going to use something like the four middle table style, so we’re going to write here in The middle table style and then four. So that’s a good idea and we’ll take a look at that.
All we’re doing is we’re just setting up the table, and we can run this macro to take a look at what it looks like.
And we see it change to green. If we change to five here, we can just run the macro again and we can see what it is changed to, and we see it changes to this great blue light. So, we can see how we can change the table style here. Changing the table style. We can do more things We can add, add additional columns if we want. So, let’s do this table referring to the table style this time we’re going to use the list columns option. What does it do? We want to do with the columns. We can count them; we can create them. We can create an element or a parent, but I simply want To Go into this case to add a column, and I want to give it a very specific name that we can say sum now. If we don’t do that, we’ll just add its own. Let’s just take a look without adding this is not so we’re going to change here, the inventory so we’re going to turn it on, and we can just see that 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 is more useful that the name equals and then we’ll give it a subtotal or tax or whatever we want. Or let’s do that here we can give it a name selected and we can run that and see that it’s also given the pay column. So, we’ve added a column, and we’ve named it. The column is fine. So, what else do we want to do with the table? We can also delete a column if we want too so. Let’s run this again: let’s keep the pay column, we’re going to add just an empty one, with no name and then We’ll go ahead and delete it. So, we can add it here, simply add the two columns so add two columns and we see that we actually have two more columns that you added, because the payment already exists, so an additional column is added and then another additional column.
So, this column added again a column without a name because the payment exists. We can’t To have two columns, the same name and this added the last column very well. So let’s go ahead and comment it out. Now, let’s delete the last two columns. How can we do that? We can do the table and then what I want to do is I want the to-do list columns and then we want to select the column that We want to delete it. So let’s say we want to delete the column, two columns, colon and then delete that delete the single column so delete the column, and what we’re going to do is we’re going to run that and what this is, what we’re going to do is delete the second Column, if we want to, we want to delete the first column all we want. What you have to do is Change this to one and it will be deleted.
If we want to add rows to the tables, we can do that we’re going to use resizing. So how are we going to do that? So, let’s go ahead and comment on this one, so we don’t do that, keep deleting the ones that are no longer their columns with that name. Now what we’re? What I’m 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 resize the table and it’s now so we’re going to ask what is the range that needs to be resized and I need To know the range of that, in The basis what I want to do is, I want to add an extra row, so we can do this using the table range property, so the range table, then we will base its resize on the current range.
So, we will resize it, and now it is as well going to base on this table. What is the size of the row, so it will be the size. A row is the number of rows plus one. So the point range does rows. How many rows are there? You calculate that we’re going to count the rows plus one, so we’re basically going to look at all the rows and we’re going to resize this table by the number of rows the sum of the rows plus one. So this It’s simply going to add a new row.
We can add as many rows as we want to add a new row, so we can drop this down a little bit and then what we’re? What we’re going to do is we’re going to run this macro here and we’re going to see that it’s created a whole new row. If we want to Run it again – and it will add another row, so we can see that it works very well, and we can also extract information from a particular table. So how can we do that?
So, we are going to write a new subroutine called here. Sub table access properties and inside we also need to cast the table as a list object. Then What we want again and we’re going to do exactly what we did here, we’re just going to set copy the table and paste it. So we can reference the same table now. What we want to do is, let’s ritual note, access to the properties that we want some information about this table and we’re going to put it in Print patch window. So, we can do the patch we’ll immediately launch a window at the moment of print patch. Let’s say I want to access the name for that table.
Name, I also want to get information about the totals, so if there are any totals, I want to know what those totals are, so we can correct the errors, Print and then show the total table. So we can do this, so if there are any totals for the table, we can show that they have not been added Yet, but we can certainly do that. We can also list the rows are counted if we want to know how many rows are present in the given table, patch Printing press, and we can do the table.
List rows number, so it will count the number of rows in the table and also what we want.
What we do is we can do body range, what we can access specific information about, one of the elements inside that how we can do that inside the table. Correction print and then maybe we want to do Something with table data so the table, and then we can do the data text range. 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 value of the first column, so this will be the first column and then the second-row value. The value of the second row will put that in the live window. So, we have there’s a lot to do son.
Let’s go ahead and display the live window, so we can see exactly what’s going to happen inside here. Okay, we’re going to bring this up a little bit. Let’s clear everything that’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 get the sales data here we have the supply totals showing because we don’t take a look at the number of rows. We see that we have here a total of 17 rows inside here. We have 15 plus Monday empty rows, totaling 17, and then we also have the value is here. The second row of the table is here on the right here.
The first row is the second row here and the first column, so it will be displayed very nicely. So it shows that we can access all the properties of the table. We can also loop through the table. If we want to do something, we can Go through all the items in the table, and it happens very quickly. So how do we do that? Do that? Let’s write a subroutine that subloop through the table and here we’re going to dimension the table again we’re going to do the exact same thing, so we just copy and paste it here as follows. We want to point to Table, and maybe we want to run a loop through the table and to do that we can dimension the cell as AR ranged because we’re going to use the cell and now what we want to do is we want to loop through The table data to do this that we can use for each loop, so every cell in the table,
Data text range: That’s what we get to and then close the next cell loop until what we want to do so, let’s do something like patch print cell Val. So what happens to loop through all the data in our table, so we can run this macro if we bring it all the way down here row by row col. So we have the first row. We have the date product quantity, so this is kind of a nice way to loop through the data we can work with. That’S very good, very easy. This was a quick and a bit of fun 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’re also able to access the table properties and loop through all the data. Of course, I’m going to with this trading is fully available through this template using the download. You will get 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 workouts every week. On. Thanks again, See you next week.
https://clickbankprofit.biz/access/268.htm
Discover more from Making Money Is Easy
Subscribe to get the latest posts to your email.
