A.N.M. Mohaimen Shanto
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 SubIf you write this code in your VBA editor, it will select the whole column “A“. To understand it better, look at the following screenshot:
Here, you can see, we have selected the whole column A with this simple line of code.
3 Suitable Ways 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.
② 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 SubWe 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.
⑤ 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 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.
② 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 SubWe 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.
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.
② 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 SubWe 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.
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:
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.
② 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.
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!
