This article demonstrates how to apply VBA codes to select columns in Excel. You may find it useful when you have to select entire ranges or columns. VBA programming codes can select entire columns or ranges automatically which will save a lot of your time. In this article, we will show you some of the methods to do that job.
Download Practice Workbook
Download this practice book to exercise the task while you are reading this article.
3 Suitable Ways to Apply VBA to Select Columns
VBA macro enables you to select columns in three different ways. You can select a single column or multiple columns or an entire range. In this section, we will go through all of these methods.
1. Run a VBA Code to Select a Single Column
Imagine a condition where you have to select an entire column using VBA codes. You can do it quite easily by applying simple code. Let’s follow these steps to learn.
- To enter a VBA code we first have to open the VBA window. You can either do it using keyboard shortcuts or from your Developer Tab. Press Ctrl+F11 to open the VBA Window.
- In the VBA window, we have to create a module to write down our code. Click on Insert, then click Module to open one.
- Here we will write our code. First, we will write the format of our code, and then after will insert the conditions. The start and end of our code is,
Private Sub Select_Column() End Sub
- We will write the code to select column C. The code is,
- The final code is,
Private Sub select_column() Columns(3).Select End Sub
- Click on the Run icon to run the code and our specified column is selected.
- You can also input a specific number in each cell of a selected column. Suppose you want to input number 100 in the C4 To do that, select any cell in column C.
- Insert this code in the module.
Private Sub select_column() ActiveCell.EntireColumn.Cells(4).Value = 100 End Sub
- Run the code and our result is here.
2. Apply a VBA Code to Select Multiple Columns
- You can select multiple columns the same way you have selected a single column. But the code is different here. So let’s start by opening the VBA window!
- We want to select columns from B to D. For that, the code is,
Private Sub select_multiplecolumns() Columns(“B:D”).Select End Sub
- And out multiple columns are selected.
3. Use a VBA Code to Select Columns in a Range
Selecting a range using VBA codes is also easy and requires a small length of code. Assume that we need to select a range from B3 to F13. Follow these steps to learn!
- Insert the VBA code into the module.
Private Sub select_range() Range(Cells(3, 2), Cells(13, 6)).Select Range("B3", "F13").Select Range("B3:F13").Select End Sub
- We have selected our range using the VBA codes.
- You can input numbers or texts in your selected range too. Just insert the code below into the module.
Private Sub select_range() Range (“B3:F13”). Select Selection = 100 End Sub
- That’s how you can do this method.
- Furthermore, you can color your selected cells too. Just write down this code into your VBA module.
Private Sub select_range() Selection.Interior.Color = RGB (255,255,0) End Sub
- And thus you can select and color your range using a VBA code.
Things to Remember
👉 If you do not have your developer tab visible, you can activate it using this instruction.
Customized Quick Access Toolbar → More Commands → Customize Ribbon → Developer → OK
We have gone through three different approaches to run VBA codes to select columns. You are most welcome to comment if you have any questions or queries. Also, you can check out our other articles related to the Excel tasks!