Using the Excel Range Columns property in VBA
The following sections explain how you can use worksheet or range object .Columns property. It begins with explaining several ways to select a column.
At the end, I will explain how to loop over the columns collection.
Set Columns as Range
The .Columns property returns a range as a collection of columns, the selected range of columns.
Which columns are included is determined by the RowIndex and ColumnIndex properties as explained below.
There are three classes that support the Columns property:
Application.Columns: All columns on the active worksheet. This is what is assumed when Columns is called without specifying the range or worksheet.Worksheets("Sheet1").Columns("B:D"): columns B, C and D on the specified worksheet.Range("B2:D3").Columns(2): The second columns in the specified range, here C.
There are two ways to identify column(s) in the Columns collection:
Another ways to identify a column is using the range address, e.g. Range("B:B"), or Range("B:D") for columns B, C and D.
N-th item in the collection
When leaving out the second optional argument ColumnIndex, the Columns property returns the nth item.
The order in which items get returned is breadth-first, so in the example table the columns with value a,b,c,...f.
| Expression | Value | Comment |
|---|---|---|
Range("B2:C3").Columns(2) | C | The second column in the range |
Range("B2:C3").Columns(3) | D | Even though the range only has two columns, ... |
| Note |
|---|
| Negative values in the index are not allowed. |
Counting columns and cells
Range("B2:C3").Columns.Count returns 2 columns, Range("B2:C3").Columns.Cells.Count returns 4 cells.
Column in range and EntireColumn
As shown in above example, Columns applied to a range only includes the cells in that range. To get the complete column, apply EntireColumn.
Dim rng As Range: Set rng = Application.Range("B2").EntireColumn
Select one or more columns in the columns collection using column character
| Expression | Column | Comment |
|---|---|---|
Range("B2:B3").Columns("B") | C | Column character interpreted as number, e.g. "B" is always 2nd column, even if not in the original range |
Range("B2:B3").Columns("B:C") | C and D | Column character interpreted as number, e.g. "B" is always 2nd column |
Loop over columns in Range
The Columns property is particularly useful because it makes it easy to iterate over a range.
The image below shows the part of the Code VBA (download) menu that lets you insert the code fragment you require.
