Download the Practice Workbook
3 Ways to Select Columns with VBA
Consider the List of Company Information dataset shown in the B4:D12 cells, which contains the Organization Name, Industry, and Country columns. We want to select the columns by applying VBA code.
Method 1 – Selecting a Single Column with VBA Code
Steps:
- Navigate to the Developer tab and click on the Visual Basic button.
- This opens the Visual Basic Editor in a new window.
- Go to the Insert tab and select Module.
- 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
Code Breakdown
Now, in the following section, we’ll explain the VBA code used to select columns with VBA.
- The subroutine’s name is select_single_col().
- The Range.EntireColumn property and Select method are used to choose column B. You can choose a different column by replacing the letter.
- Click on the Run button.
- The results should look like the image given below.
Method 2 – Applying VBA Code to Select Multiple Contiguous Columns
Steps:
- Open a VBA module (you can follow the previous Method for the steps).
- Insert the following code:
Sub select_multi_cols()
Columns("B:D").Select
End Sub
Code Breakdown
- The subroutine’s name is select_multi_cols().
- The Columns property and Select method will select columns B through D. You can change the range.
- Hit the F5 key to execute the macro.
- Here’s the result.
Method 3 – Choosing a Range Using VBA Code
Steps:
- Create a new VBA module as shown previously.
- Insert the following VBA code into the Module.
Sub select_range_of_cols()
Range("B6:D9").Select
End Sub
Code Breakdown
- The subroutine’s name is select_range_of_cols().
- The Range property and Select method will choose the B6:D9 cells. You can alter this range as you wish.
- Hit the Run button.
- Here’s the result.
How to Select Multiple Columns by Number with VBA Code
Steps:
- Open the Visual Basic editor and insert a Module.
- Copy and paste the code given below into the module.
Sub select_multi_cols_by_number()
Columns(2).Resize(, 3).EntireColumn.Select
End Sub
- Press the Run button or the F5 key.
- Here’s the result.
How to Fix the Issue of Selecting Columns with VBA Not Working
Steps:
- In this case, Excel returns an error message when we attempt to run the code. We have to use the worksheet name to correctly run the code.
- Correct the code as shown below.
Sub Fixing_select_col_error()
Worksheets("Columns Select Not Working").Columns("B:D").Select
End Sub
- Click the Run button.
- The required columns are selected as evident in the image below.
How to Enable the Developer Tab
Steps:
- Navigate to the File tab.
- Choose Options from the list.
- In the Excel Options window, press Customize Ribbon.
- Check the Developer tab from the list on the right and click OK.
- 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.
