We select multiple columns in Excel for various reasons. For example, choosing numerous columns allows us to simultaneously apply formatting changes, such as fill color, border, and font, to multiple columns.
We can select various columns manually; however, choosing the columns using VBA has many advantages, such as:
- Selecting multiple columns using VBA is faster than selecting columns manually.
- Excel VBA procedures can be saved and reused, making it easy to repeat complex tasks with a single click.
- Selecting multiple columns using Excel VBA provides greater flexibility and customization options.
This tutorial shows four techniques for selecting multiple Excel columns using VBA in sequential and non-sequential order.
How to Select Multiple Columns in Sequential Order Using Excel VBA
This section will look at selecting multiple columns in sequential or consecutive order.
Method #1: Use the Range Object and the Select Method to Select Multiple Sequential Columns
We can use the Range object and the Select method to select multiple Excel columns in sequential order.
Suppose we have the following dataset showing GDP statistics for various countries.
We want to use Excel VBA to select the sequential columns A, B, and C.
We use the below steps:
- Activate the worksheet containing the dataset.
- Press Alt + F11 to open the Visual Basic Editor.
- Open the Insert menu and choose the Module option to insert a new module.
- Copy the following sub-procedure and paste it into the module.
|
1 2 3 4 5 |
Sub SelectSequentialColumns() Dim rng As Range Set rng = Range("A:C") 'assign the sequential columns to the declared variable rng.Select 'select the sequential columns End Sub |
- Save the procedure and save the workbook as a Macro-Enabled Workbook.
- Place the cursor anywhere in the code and press F5 to execute the sub-procedure.
- Switch to the active workbook by pressing Alt + F11 or clicking the View Microsoft Excel button on the toolbar.
We notice that columns A, B, and C are selected.
Method #2: Use the Application Object, the Union Function, and Select Method to Select Sequential Columns
We can use the Application object, the Union function, and the Select method to select sequential columns in Excel.
We have the following example dataset showing the GDP statistics of various countries.
We want to use the Application object, the Union function, and the Select method in Excel VBA to select the sequential columns A, B, and C.
We proceed as follows:
- Activate the worksheet containing the dataset.
- Press Alt + F11 to open the Visual Basic Editor.
- Open the Insert menu and choose the Module option to insert a new module.
- Copy the following procedure and paste it into the module.
|
1 2 3 |
Sub SelectSequentialColumns2() Application.Union(Columns("A"), Columns("B"), Columns("C")).Select End Sub |
- Save the procedure and save the workbook as a Macro-Enabled Workbook.
- Click anywhere in the sub-procedure and press F5 to execute the code.
- Switch to the active workbook by clicking the View Microsoft Excel button on the toolbar or pressing Alt + F11.
We notice that columns A, B, and C have been selected.
How to Select Multiple Columns in Non-Sequential Order Using Excel VBA
This section will look at how to select multiple columns in non-sequential order.
Method #1: Use the Range Object and the Select Method to Select Multiple Non-Sequential Columns
We can use the Range object and the Select method to select multiple Excel columns in non-sequential order.
Presume we have the following dataset showing GDP statistics for various countries.
We want to use Excel VBA to select the non-sequential columns A, C, and E.
We use the below steps:
- Activate the worksheet containing the dataset.
- Press Alt + F11 to open the Visual Basic Editor.
- Open the Insert menu and choose the Module option to insert a new module.
- Copy the following procedure and paste it into the module.
|
1 2 3 4 5 |
Sub SelectNonSequentialColumns() Dim rng As Range Set rng = Range("A:A,C:C,E:E") 'assign the non-sequential columns to the declared variable rng.Select 'select the non-sequential columns End Sub |
- Save the procedure and save the workbook as a Macro-Enabled Workbook.
- Place the cursor anywhere in the procedure and press F5 to execute the procedure.
- Switch to the active workbook by pressing Alt + F11 or clicking the View Microsoft Excel button on the toolbar.
We notice that columns A, C, and E are selected.
Method #2: Use the Application Object, the Union Function, and Select Method to Select Non-Sequential Columns
We can use the Application object, the Union function, and the Select method to select non-sequential columns in Excel.
We have the following example dataset showing the GDP statistics of various countries.
We want to use the Application object, the Union function, and the Select method in Excel VBA to select the non-sequential columns A, C, and E.
We use the following steps:
- Activate the worksheet containing the dataset.
- Press Alt + F11 to open the Visual Basic Editor.
- Open the Insert menu and choose the Module option to insert a new module.
- Copy the following procedure and paste it into the module.
|
1 2 3 |
Sub SelectNonSequentialColumns2() Application.Union(Columns("A"), Columns("C"), Columns("E")).Select End Sub |
- Save the procedure and save the workbook as a Macro-Enabled Workbook.
- Click anywhere in the procedure and press F5 to execute the procedure.
- Switch to the active workbook by clicking the View Microsoft Excel button on the toolbar or pressing Alt + F11.
We notice that columns A, C, and E have been selected.
Conclusion
Selecting multiple columns using Excel VBA has many benefits compared to manually selecting columns. This tutorial showed various techniques for selecting sequential and non-sequential columns in Excel using Excel VBA. We hope you found the tutorial helpful.