VBA is, as you are probably aware by now, a great tool for the automation of work in Excel. It can be used to automate any action whatsoever.
Selection of the stuff that we want to do something with is the first step that we can do when automating our work. In the example below, we will show how to select columns in the table with VBA.
Select Column in Table Using VBA
First things first, we need to create a table with the data in Excel. We will create it out of our data for NBA players:
This is simple data, and it is not organized as a table. To do so, we will select our data, and then go to Insert >> Tables >> Insert Table:
Now our data will be organized in a table, and we can click on it and change the name of the table in the Table Design tab:
Now we need to create our code to select a particular column in the table. For this, we need to open up our VBA module. To do this, we need to open the Excel file, click on ALT + F11, and then right-click on the left window that appears, go to Insert >> Module:
The new window will appear on the right side of the screen (blank window) and we can type our code in it. For our example, we will select column C (the third column in our table). To make this happen, all we need to do is type in this simple line of code:
|
1 2 3 |
Sub SelectingColumns() ActiveSheet.ListObjects("NBA_players").ListColumns(3).Range.Select End Sub |
To execute the code, we will simply press F5 while in the module. Once we do, column C will be selected in our table:
A couple of things should be noted here. First, we used ActiveSheet reference, which means that we assumed that the user will be positioned on a particular sheet. We could have also used Sheets reference, not to reference a certain sheet.
The important thing is to use ListObjects, because it is the part of the code that will allow us to choose one of our objects, in our case our table. We use the table name to directly position ourselves in it, and we use ListColumns to provide the number of column that we want to address.
For the final step, we select the whole column. Of course, after all the selection, we can proceed with any kind of action that we want.