Excel VBA: Select Multiple Columns (3 Methods)

It’s simple enough to select multiple columns manually in Excel. But it’s also a simple task using VBA codes.

Select Single Column Using VBA

Entering the following simple code in the VBA Editor will select a single column, in this case Column A:

Sub select_single_column()

Range("A:A").Select

End Sub

Select Multiple Columns using VBA

Method 1 – Using Range.Select

This is the same method used above to select a single column, and can be used to select multiple columns in sequential or non-sequential order.

Steps

• Press Alt+F11 to open the VBA editor.
• Select Insert > Module.

• To select multiple columns in non-sequential order, enter the following code:

Sub Range_select_method()

Range("A:A,C:C,E:E").Select

End Sub

We are selecting columns A, C, and E.

• Save the file.
• Press Alt+F8 to open the macro dialog box.
• Select Range_select_method.
• Click on Run.

• To select columns sequentially from A to E, enter the following code:

Sub Range_select_method()

Range("A:E").Select

End Sub

• Click on Run.

Multiple columns are selected as specified in the code.

Method 2 – Using Application.Union

Application.Union method in VBA returns the union of two or more ranges. We can use this method to select multiple columns non-sequentially.

Steps

• Press Alt+F11 to open the VBA editor.
• Select Insert > Module.

• Enter the following code:

Sub Application_Union()

Application.Union(Columns("A"), Columns("E"), Columns("C")).Select

End Sub

We are selecting columns A, C, and E.

• Save the file.
• Press Alt+F8 to open the macro dialog box.
• Select Application_Union.
• Click on Run.

Multiple columns are selected as specified.

Method 3 – Using EntireColumn.Select

Suppose you find a value in cell A2 and then want to select the entire column. The Application.Union method in conjunction with EntireColumn.Select can accomplish this task. This method is also appropriate if you find multiple values in different cells.

Steps

• Press Alt+F11 to open the VBA editor.
• Select Insert > Module.

• To select multiple columns in non-sequential order, type the following code:

Sub EntireColumn_Select()

Application.Union(Range("A1"), Range("C5"), Range("E2")).EntireColumn.Select

End Sub

We are selecting columns A, C, and E.

• Save the file.
• Press Alt+F8 to open the macro dialog box.
• Select EntireColumn_Select.
• Click on Run.

Multiple columns are selected as specified.

Using VBA to Select Multiple Columns from a Portion of a Dataset

You may want to select all the data from multiple columns without the header. To demonstrate the method, we are going to use this dataset:

Our goal is to select just all the data from the columns Name and Joining Date. We will accomplish this with the Range.Select method of VBA.

Steps

• Press Alt+F11 to open the VBA editor.
• Select Insert > Module.

• To select multiple columns in non-sequential order, enter the following code:

Sub select_dataset()

Range("B5:B10, D5:D10").Select

End Sub

• Save the file.
• Press Alt+F8 to open the macro dialog box.
• Select select_dataset.
• Click on Run.

The specified cells in multiple columns are selected.

Things to Remember

✎ The first 3 methods select the entire column. If you want to select only a range within multiple columns, use the last method.

✎ You can always select the entire column with your mouse. Just press Ctrl and click on the column names to select multiple columns.

Download Practice Workbook