Excel VBA: Select Multiple Columns (3 Methods)

Selecting multiple columns in Excel is a common task. We select numerous columns to perform various operations. You can pick multiple columns manually. But, if you love to work with Excel’s VBA codes, you might want to select numerous columns with that. We will show you how to select multiple columns using VBA in Excel with appropriate examples and reasonable explanations. So, stay tuned!


Download Practice Workbook

Download this practice workbook


Select Single Column Using VBA in Excel

Now, before we start, I think we should discuss selecting a single column using VBA. It is pretty simple. You have to write a single line of code to choose a single column.

Look at this simple code:

Sub select_single_column()

Range("A:A").Select

End Sub

If you write this code in your VBA editor, it will select the whole column “A“. To understand it better, look at the following screenshot:

select single column in excel

Here, you can see, we have selected the whole column A with this simple line of code.


3 Methods to Select Multiple Columns using VBA in Excel

Now, we have come to the main point. We can select multiple columns using VBA in Excel. In the upcoming sections, we are providing you with three VBA code snippets that you can apply to your worksheet. We recommend you learn and implement all these methods to your dataset to enrich your Excel knowledge.

1. Using Range.Select in VBA to Select Multiple Columns

We have already discussed this method in the previous section. This method is pretty easy to use. You may make this method your go-to in my opinion. You can select multiple columns in sequential or non-sequential order. In the following steps, we will show you both.

📌 Steps

First, press Alt+F11 to open the VBA editor. Select Insert > Module.

insert vba module

Then, to select multiple columns in non-sequential order, type 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.

Now, save the file. Then, press Alt+F8 to open the macro dialog box. Select Range_select_method.

Then, click on Run.

vba select multiple columns in excel

If you want to select columns from A to E, type the following code:

Sub Range_select_method()

Range("A:E").Select

End Sub

After that, click on Run.

As you can see, we are successful to select multiple columns using VBA in Excel.

2. Use of the Application.Union method of VBA in Excel

Now, Application.Union method in VBA basically returns the union of two or more ranges. We can use this method to select multiple columns in Excel. If you want to choose multiple columns in a non-sequential manner, you can use this. It will come in handy in the future.

📌 Steps

First, press Alt+F11 to open the VBA editor. Select Insert > Module.

insert vba module

Then, to select multiple columns in non-sequential order, type 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.

Now, save the file. Then, press Alt+F8 to open the macro dialog box. Select Application_Union.

Then, click on Run.

vba select multiple columns in excel

Finally, we are successful to select multiple columns using VBA in Excel. Use this code to your worksheet to choose numerous columns in Excel.

3. EntireColumn.Select Method to Select Multiple Columns in Excel

Here, we are also using Application.Union method but adding a simple portion EntireColumn.Select. Suppose, you found a value in cell A2. After that, you want to select the entire column. You can use  EntireColumn.Select method to pick the whole column.

We are combining this with the Application.Union method to select multiple columns. If you find multiple values in different cells, you can choose this method.

📌 Steps

First, press Alt+F11 to open the VBA editor. Select Insert > Module.

insert vba module

Then, 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.

Now, save the file. Then, press Alt+F8 to open the macro dialog box. Select EntireColumn_Select.

Then, click on Run.

vba select multiple columns in excel

As you can see, we are successful to select multiple columns using VBA in Excel.


Select Multiple Columns Using VBA from a Portion of Dataset in Excel

Now, there might be a situation where you may need to pick a column from a dataset. You may want to select all the data from multiple columns without the header. In this case, you need to bring a slight change in the coding.

To demonstrate this, we are going to use this dataset:

Here, we have a dataset of some persons. Our goal is to select all the data from the column Name and Joining date. We will do that with the Range.Select method of VBA.

📌 Steps

First, press Alt+F11 to open the VBA editor. Select Insert > Module.

insert vba module

Then, to select multiple columns in non-sequential order, type the following code:

Sub select_dataset()

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

End Sub

Now, save the file. Then, press Alt+F8 to open the macro dialog box. Select select_dataset.

Then, click on Run.

Finally, we are successful to select multiple columns using VBA in Excel

As you can see, we are successful to select all the data from multiple columns using VBA in Excel. I hope it has increased your knowledge.


💬 Things to Remember

The previous 3 methods actually select the entire column. So, if you want to select multiple columns from a range, use the method shown earlier.

You can always select the entire column with the mouse. Just press Ctrl and click on the column names.


Conclusion

To conclude, I hope this tutorial has provided you with a piece of useful knowledge to select multiple columns using VBA in Excel. We recommend you learn and apply all these instructions to your dataset. Download the practice workbook and try these yourself. Also, feel free to give feedback in the comment section. Your valuable feedback keeps us motivated to create tutorials like this.

Don’t forget to check our website Exceldemy.com for various Excel-related problems and solutions.

Keep learning new methods and keep growing!


Related Articles

Tags:

Shanto

Shanto

Hello! I am Shanto. An Excel & VBA Content Developer. My goal is to provide our readers with great tutorials on various Excel-related problems. I hope our easy but effective tutorials will enrich your knowledge. I have completed my BSc in Computer Science & Engineering from Daffodil International University. Working with data was always my passion. Love to work with data, analyze those, and find patterns. Also, love to research. Always look for challenges to keep me growing.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo