How to Apply VBA to Select Columns (3 Methods)

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.

Step 1:

  • 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.

Run a VBA Code to select a single Columns

  • In the VBA window, we have to create a module to write down our code. Click on Insert, then click Module to open one.

Run a VBA Code to select a single Columns

Step 2:

  • 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

Run a VBA Code to select a single Columns

  • We will write the code to select column C. The code is,
Columns(3).Select
  • The final code is,
Private Sub select_column()
Columns(3).Select
End Sub

Run a VBA Code to select a single Columns

  • Click on the Run icon to run the code and our specified column is selected.

Run a VBA Code to select a single Column

Step 3:

  • 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.

Run a VBA Code to select a single Column

  • Insert this code in the module.
Private Sub select_column()
ActiveCell.EntireColumn.Cells(4).Value = 100
End Sub

Run a VBA Code to select a single Column

  • Run the code and our result is here.

Run a VBA Code to select a single Column


2. Apply a VBA Code to Select Multiple Columns

Step 1:

  • 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!

Apply a VBA Code to Select Multiple Columns

Step 2:

  • We want to select columns from B to D. For that, the code is,
Private Sub select_multiplecolumns()
Columns(“B:D”).Select
End Sub

Apply a VBA Code to Select Multiple Columns

  • And out multiple columns are selected.

Select Multiple Columns


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!

Select Range

Step 1:

  • 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

Select Range

  • We have selected our range using the VBA codes.

vba columns select

Step 2:

  • 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

vba columns select

  • That’s how you can do this method.

select range

Step 3:

  • 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

select range

  • And thus you can select and color your range using a VBA code.

select range


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


Conclusion

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!


Similar Articles to Explore

Asikul Himel
We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo