Excel VBA: Select Multiple Columns (3 Methods)

Get FREE Advanced Excel Exercises with Solutions!

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!

vba select multiple columns


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.


How to Select Multiple Columns using VBA in Excel: 3 Suitable Ways

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.

Selecting Multiple Columns

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


2. Use 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.


How to 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:

A dataset that consists of four fields

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.


Download Practice Workbook

Download this practice workbook


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.

Keep learning new methods and keep growing!

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
A.N.M. Mohaimen Shanto
A.N.M. Mohaimen Shanto

A.N.M. Mohaimen Shanto, a B.Sc. in Computer Science and Engineering from Daffodil International University, boasts two years of experience as a Project Manager at Exceldemy. He authored 90+ articles and led teams as a Team Leader, meticulously reviewing over a thousand articles. Currently, he focuses on enhancing article quality. His passion lies in Excel VBA, Data Science, and SEO, where he enjoys simplifying complex ideas to facilitate learning and growth. His journey mirrors Exceldemy's dedication to excellence and... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo