4. Add or Delete Table Columns

In this unit, we’re going to look at how to add and delete columns to the table with VBA. We’ll jump into the VB editor here, and I’m in the add columns to table macro. Same setup here, with the list object variable. And then down here, we have very simple line of code that will add a list column, or just add a column, to the right side of the table. This is the list columns dot add method. It does have one parameter here. If we list the parameter, we just hit the space bar, here, we can see the position parameter. That allows us to reference a column position, which we’ll talk about in just a second. But if we don’t reference that parameter, then this will just add a column to the right side of the table. We can go ahead and step through this and see this. Let me just move the window over here. If we just hit F8 and step through this, this, again, is going to add that column right over here to the right side of the table. Now, if we do specify that position parameter, this will add a column to the left of whichever column we reference here. So, if we reference column one as the position, then this will add a column to the left of column one. Running this line of code, hit F8 again, we’ll now see we have new column right over here to the left of column one. Kind of the trick to remember there, is if we do just add with no position, then that’s going to add a column to the end, or to the right of the last column in the table. If we do use the position, this will always add a column to the left of whichever column we reference. I just hit F8 to stop running that. And of course, we can also delete columns from the table using the delete method. Here, with the delete method, it’s very similar. Except in this case, we’ll reference either the column by its name, or its number within the list column’s property. So if we type out this LO dot list columns and tab into that property, open the parentheses, you see we have an index there, which would be a collection of all of the columns in the table, and we can either reference the column by its number … its column number within the table, or its name. So in this line of code right up here, you could see we have the name of the column in parentheses. If we wanted to, for example, always delete the order ID column, something like that, we can just reference it by its name, dot, and then we’re going to run that delete method on that list column. I actually want to delete the first column out of there. That column two over at the left of the table, here. Let’s delete that one. So we can just refer to that as the first column in the table, and just change that to column one, step through this, and hit F8 there, and we can see now, column one has been deleted. And the delete method is only going to delete one column at a time. And, again, we just reference that column right here by either its name or its index number. So that’s how to add and delete columns. In the next video, we’re going to take a look at the list columns object.