Select Column in Table Using VBA

VBA is, as you are probably aware by now, a great tool for 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:


Description automatically generated

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:

Graphical user interface, application, table

Description automatically generated

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:

Graphical user interface, application

Description automatically generated

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:

Sub SelectingColumns()
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:

Graphical user interface, text, application, email

Description automatically generated

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 the 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.

Posted in vba