[Guide] How to Use Column Select in VBA

Contents

Selection of a Data Range in Excel

In a Microsoft Excel document, serval operations like Delete, Edit, Type, and other formatting can also be done only if we select the range of cells on which we need to perform the change. There are several ways through which we select the range of cells. In one of my earlier articles, I explained the selection of rows. In this article, we will discuss how to select specific column(s) using VBA to format or perform any action on them.

Select a Column Knowing its Number.

Here is the piece of code that helps in selecting a specific column if we know the column number. Let us select column number 9 here.


Sub column_select()
'Select the column no 9
Columns(9).Select
End Sub

Output:

Here I already have a sheet named “Source” with some data in it. Column number 9 of the active sheet is selected on running the code.

An Excel sheet named “Source” with some data in it. Column number 9 of the active sheet is selected on running the code.

Now let us use the column letter and rewrite the code.

Sub column_select()

'Select the column no 8 ( the H column )
Columns("H").Select

End Sub

Output:

On running this piece of code, the column “H” gets selected.

Same Excel sheet, this time with column H selected.

Using the EntireColumn Property

This is a property of a cell reference. It can be used in two ways as explained below.

Using a Range Reference

We select the entire column to which a specified cell belongs.

Sub column_select()
'Select the cols that belongs to Cell G5
Range("G5").EntireColumn.Select

End Sub

Output:

Since G5 belongs to column G or column number 7, that column is selected here.

Same Excel sheet with Column G selected.

Using a Cell Reference

Sub column_select()
'Select the cols that belongs to Cell (3, 6) i.e.  6th col
Cells(3, 6). EntireColumn.Select
End Sub

Output:

The cell reference quoted in the code cells (3,6) belongs to the 6th column. So, that column is highlighted here.

Select One or More Columns:

The Range object can be used to select 1 or more columns too.

The code below selects all columns from 5 to 10.

Sub column_select2()
'Select the cols from E to J
Range("E:J").EntireColumn.Select
End Sub

Output:

The columns that belong to the mentioned range have all been selected. i.e. E to J.

An Excel sheet where columns in a specific range (E to J) have been selected.

The same can be done using cell references but not the column numbers alone.

For ex: Range(“D6:K7”). EntireColumn.Select is valid

But Range(“6:7”). EntireColumn.Select is not valid

Let us see another example to understand this.

Select All Columns That Belong to a Specific Range

Let us assume a specific range C9 to F8. Now, if we use the EntireColumn property on this range and select it, we can notice that the columns C to F have been selected because the specified range belongs to these four columns.


Sub column_select2()

' Select the cols from C to F
Range("C9:F8").EntireColumn.Select

End Sub

Output:

Excel sheet with the EntireColumn property used, selecting columns C to F.

Selection of Column to Which the Active Cell Belongs

Run the code below after selecting the cell (7, 5) or E7 manually.


Sub col_select()

'Select the cols to which the active cell belongs
ActiveCell.EntireColumn.Select

End Sub

Output:

The column of the cell selected manually is “E” because the cell is E7. So, that column has been selected for running the macro.

Excel sheet with Column E selected manually

Selecting All Columns of the Active Sheet

All columns of the active sheet (“Destination” sheet in this example) are selected.


Sub col_select2()

'Select the cols of the active sheet
ActiveSheet.Columns.Select

End Sub

Output:

All the columns of the “Destination” sheet (which was kept active) have been selected.

Excel sheet where all the columns have been selected.

Select a Specific Column in a Range

In this example, the columns are not selected directly from the sheet. Instead, a specified range is assumed as a different sheet and the column number specified within the code is assumed as the column number within the same specified range and selected. For example, we want to select the third column of the range E5 to K10. The third column of this range is column number 7 or “G” of the spreadsheet i.e. the third column from column E.

Sub col_select3()

'Select the col within the range
Range("E5:K10").Columns(3).Select

End Sub

Output:

The range specified in the code is E5 to K10 which is highlighted using a green border for your reference. The third column of this range has been selected for running the code.

Excel sheet where a green border is showing the portion of column G (E5 to K10) that has been selected.

Color Columns That Have Data in the Second Row

In this example, we will consider the first 15 columns and go through the content of the second row in each column. If the cell is not null, then we will color the related column in pink. If the cell is null (or empty), we will leave it undisturbed.

Sub col_select_Colour()

' Loop through the first 15 columns
For i = 1 To 15

' validate if the cell is blank
If Cells(2, i).Value &<&>"" Then

'select the col
Columns(i).EntireColumn.Select

' colour the selected col
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.Color = 9396474
.TintAndShade = 0
.PatternTintAndShade = 0
End With
' end the with block
End If
' end the loop
Next

End Sub
An Excel sheet where any cells that are not null are highlighted in pink.

The second row which has been tested for empty cells in the code is highlighted with a black border here. The non-empty cells have been considered in the conditions and the columns to which these non-empty cells belong have been colored as per the VBA code.

Conclusion

The column/row range selection/modification can be done with loops and conditions in the VBA program using proper logic. However, unlike a manual task, most of the tasks on VBA can also be done directly without the selection of cells/rows/columns/range. So, the selection operation is not always mandatory. In fact, it is quick if we can directly do the operations on the cells without selecting while using VBA code.

Leave a Reply

Your email address will not be published. Required fields are marked *