Visual Basic for Applications (VBA) can be used for virtually everything that we want. When you start working with it, you simply cannot stop automating every single step of your work.
One of these things is a selection of range, whether that be a certain cell, column, or sheet. In the example below, we will show multiple ways in which you can select a range of columns in your worksheet.
Select a Range of Columns with Range.Select
The first thing that we need to do is to open the VBA editor. To do it, we press ALT + F11 on our keyboard. After that, on the window that appears, we will right-click on the left window and choose Insert >> Module:
A window will appear on the right side of the window, and we can start writing our code. This will be the code for selecting the cells B, D, and E:
|
1 2 3 |
Sub Selecting_Columns_Range_Select() Range("B:B,D:D,E:E").Select End Sub |
When we execute the code by pressing F5 on our keyboard, this is what we get:
We have used a simple range and select statement to select the columns in a non-sequential order. If we want to do it in sequential order, it would be even easier. Let’s select a range of columns from B to E:
|
1 2 3 |
Sub Selecting_Columns_Range_Select_2() Range("B:E").Select End Sub |
This is the result we end up with and the code in our module:
Select a Range of Columns with the Application.Union
This option can also be used for selecting the columns in a non-sequential way. If we want to select columns B, D, and F with Application.Union, this will be our code:
|
1 2 3 |
Sub Selecting_Columns_Application_Union() Application.Union(Columns("B"), Columns("D"), Columns("F")).Select End Sub |
When we execute the code, this is what we end up with:
And we can also see our code on the right of our VBA editor.
Select a Range of Columns with EntireColumn.Select
We can use EntireColumn.Select to find a certain cell and then select the entire column where this cell is located. For our example, we will presume that we want to select the entire column where cell A1 is found. This is our code:
|
1 2 3 |
Sub Selecting_Columns_EntireColumn() Range("A1").EntireColumn.Select End Sub |
And this is what happens when we execute it:
