How to Select Columns with VBA (3 Easy Ways)

In Microsoft Excel, VBA Macros help to solve various problems and automate repetitive tasks. Now, the selection of columns is one such task that we can automate using Excel’s VBA. In this article, we’ll explore 3 easy ways how to select columns with VBA. In addition, we’ll also learn to select multiple columns by number and fix selecting column not working issue.
The GIF below is an overview of the article which represents the selection of columns with VBA code.

In the following sections, we’ll discuss the dataset and learn about each method step-by-step.

Download Practice Workbook

3 Ways to Select Columns with VBA

In the first place, let’s consider the List of Company Information dataset shown in the B4:D12 cells which contains the “Organization Name”, “Industry”, and “Country” columns respectively. Here, we want to select the columns by applying the VBA code, henceforth, let’s glance at each method individually.

Here, we have used the Microsoft Excel 365 version; you may use any other version at your convenience.

1. Selecting a Single Column with VBA Code

First of all, imagine a condition where you have to select an entire column using VBA macros. In fact, you can do it quite easily by applying simple code. Let’s follow these steps to learn.

📌 Steps:

• First, navigate to the Developer tab >> click the Visual Basic button.

Now, this opens the Visual Basic Editor in a new window.

• Secondly, go to the Insert tab >> select Module.

For ease of reference, copy the code from here and paste it into the window as shown below.

Sub select_single_col()

Range("B:B").EntireColumn.Select

End Sub

• Third, click the Run button.

Finally, the results should look like the image given below.

2. Applying VBA Code to Select Multiple Columns

For one thing, we can select multiple columns in a similar way to selecting a single column. Here, there is a slight modification to the code, so let’s dive in!

📌 Steps:

• Initially, run steps from the previous method to open the VBA window, insert a new Module  >> enter the VBA code.

Sub select_multi_cols()
Columns("B:D").Select
End Sub

• At this point, hit the F5 key to execute the macro.

Voila! The output should resemble the picture shown below.

3. Choosing Range of Columns Using VBA Code

Alternatively, we can also select a range of columns using the VBA code. Now, follow these steps to learn!

📌 Steps:

• Similarly, perform the steps shown previously <anchor text> >> insert the VBA code into the Module.

Sub select_range_of_cols()
    Range("B6:D9").Select
End Sub

• Following this, tap the Run button.

Finally, the end result appears in the screenshot given below.

How to Select Multiple Columns by Number with VBA Code

Now, what if you want to select columns by numbers using the VBA code? Then you’re in luck! Because our next method answers this exact question.

📌 Steps:

• At the very beginning, open the Visual Basic editor and insert Module  >> copy and paste the code given below.

Sub select_multi_cols_by_number()
Columns(2).Resize(, 3).EntireColumn.Select
End Sub

• In turn, press the Run button or the F5 key.

Subsequently, your output should resemble the figure below.

How to Fix the Issue of Selecting Columns with VBA Not Working

Last but not least, if you’re experiencing issues selecting columns with VBA, then do not despair! Since the solution is described in the steps below.

📌 Steps:

• In this case, Excel returns an error message when we attempt to run the code. In this situation, we have to use the worksheet name to correctly run the code.

• Afterward, correct the code as shown below.

Sub Fixing_select_col_error()
Worksheets("Columns Select Not Working").Columns("B:D").Select
End Sub

• From this point, click the Run button.

Consequently, the required columns are selected as evident in the image below.

Things to Remember

As a note, here is something to ponder upon when running VBA codes. Usually, the Developer tab is hidden by default, hence to run the VBA script we have to enable the Developer tab.

📌 Steps:

• At the start, navigate to the File tab.

• Immediately, choose Options from the list.

• Later, in the Excel Options window, press Customize Ribbon >> tick the Developer tab >> tap OK.

Eventually, the Developer tab appears as shown in the picture below.

Practice Section

We have provided a Practice section on the right side of each sheet so you can practice yourself. Please make sure to do it by yourself.

Conclusion

In essence, this article shows 3 effective methods on how to select columns with VBA. So, read the full article carefully, we hope you find this article helpful. Now, if you have any further queries or recommendations, please feel free to comment here. Lastly, visit ExcelDemy for many more articles like this.