The Columns of a Worksheet

Columns Fundamentals

In VBA for Microsoft Excel, to programmatically refer to a column, you use a collection. You can use Range. If you want to get a reference to a column or a group of columns, declare a variable of type Range:

Sub Exercise()
    Dim Series As Range
End Sub

To initialize the variable, you will identify the workbooks and the worksheets you are using. We will see various examples later on.

To refer to a column, pass its index to the parentheses of the Columns collection. Here are two examples:

Sub Exercise()
    REM This refers to the first column
    Workbooks(1).Worksheets(2).Columns(1)
    ' This refers to the 12th column
    Workbooks(1).Worksheets(2).Columns(12)
End Sub

You can omit calling the Workbooks(1) property to identify the first workbook if you know you are referring to the default workbook. Therefore, the above code can be written as follows:

Sub Exercise()
    REM This refers to the fourth column
    Worksheets(2).Columns(4)
End Sub

When this code runs, Microsoft Excel must be displaying the second worksheet. If you run this code while Microsoft Excel is displaying a worksheet other than the second, you would receive an error:

If you want to access a specific column in any worksheet from the workbook, you can omit indicating the worksheet from the Worksheets collection. Here is an example:

Sub Exercise()
    REM This refers to the fourth column
    Columns(4)
End Sub

This time, the code indicates that you are referring to the fourth column of whatever worksheet is currently active.

To refer to a column using its name, pass its letter or combination of letters as a string in the parentheses of the Columns collection. Here are two examples:

Sub Exercise()
    Rem This refers to the column named/labeled A
    Columns("A")
    ' This refers to the column named DR
    Columns("DR")
End Sub

To programmatically refer to adjacent columns, you can use the Columns collection. In its parentheses, type the name of a column that would be on one end of the range, followed by a colon ":", followed by the name of the column that would on the other end. Here is an example that refers to columns in the range D to G:

Sub ColumnReference()
    Rem Refer to the range of columns D to G
    Columns("D:G")
End Sub

You can also select columns using the Range class. To do this, type the name of the first column, followed by a colon, followed by the name of the column on the other end. Here is an example:

Sub ColumnReference()
    Rem This refers to the columns in the range D to H
    Range("D:H")
End Sub

To programmatically refer to non-adjacent columns, use the Range collection. In its parentheses, type each name of a column, followed by a colon, followed by the same name of column, then separate these combinations with commas. Here is an example:

Sub Exercise()
    Rem This refers to Columns H, D, and B
    Range("H:H, D:D, B:B")
End Sub

To refer to all columns of a worksheet, use the Columns name. Here is an example:

Sub Exercise()
    Columns
End Sub

To support column selection, the Column class is equipped with a method named Select. This method does not take any argument. To select the fourth column using its index, you would use code as follows:

Sub Exercise()
    Rem This selects the fourth column
    Columns(4).Select
End Sub

To select a column using its name, you would use code as follows:

Sub Exercise()
    Rem This selects the column labeled ADH
    Columns("ADH").Select
End Sub

When a column has been selected, it is stored in an object called Selection. You can then use that object to take an action to apply to the column.

To programmatically select a range of columns, in the parentheses of the Columns collection, enter the name of the first column on one end, followed by a colon ":", followed the name of the column that will be at the other end. Here is an example:

Sub Exercise()
    Rem This selects the range of columns from Column D to Column G
    Columns("D:G").Select
End Sub

You can use this same notation to select one column. To do this, use the Range collection. In the parentheses of the collection, enter the name of the column, followed by a colon, followed by the same column name. Here is an example:

Sub Exercise()
    Rem This selects Column G
    Range("G:G").Select
End Sub

To programmatically select non-adjacent columns, use the technique we saw earlier to refer to non-adjacent columns, then call the Select method. Here is an example:

Sub Exercise()
    Rem This selects Columns B, D, and H
    Range("H:H, D:D, B:B").Select
End Sub

When many columns have been selected (whether adjacent or not), their selection is stored in an object named Selection. You can access that object to apply a common action to all selected columns.

To support the creation of columns, the Column class is equipped with a method named Insert. This method takes no argument. When calling it, you must specify the column that will succeed the new one. Here is an example that will create a new column in the third position and move the columns from 3 to 16384 to the right:

Sub CreateColumn()
    Columns(3).Insert
End Sub

To programmatically add new columns, specify their successors using the Range class as we saw earlier, then call the Insert method of the Column class. Here is an example that creates new columns in places of Columns B, D, and H that are pushed to the right to make place for the new ones:

Sub CreateColumns()
    Range("H:H, D:D, B:B").Insert
End Sub

To provide the ability to delete a column, the Column class is equipped with a method named Delete. This method does not take an argument. To delete a column, use the Columns collection to specify the index or the name of the column that will be deleted. Then call the Delete method. Here is an example that removes the fourth column. Here is an example:

Sub DeleteColumn()
    Columns("D:D").Delete
End Sub

To programmatically delete many adjacent columns, specify their range using the Columns collection and call the Delete method. Here is an example:

Sub DeleteColumns()
    Columns("D:F").Delete
End Sub

To delete many non-adjacent columns, use the Range class then call the Delete method of the Column class. Here is an example that deletes Columns C, E, and P:

Sub DeleteColumns()
    Range("C:C, E:E, P:P").Delete
End Sub

To support column sizes, the Column class is equipped with a property named ColumnWidth. Therefore, to programmatically specify the width of a column, access it, then access its ColumnWidth property and assign the desired value to it. Here is an example that sets Column C's width to 4.50:

Sub Exercise()
    Columns("C").ColumnWidth = 4.5
End Sub

To use AutoFit Selection, first select the column(s) and store it (them) in a Selection object, access its Columns property, then call the AutoFit method of the Columns property. This can be done as follows:

Private Sub Exercise()
    Selection.Columns.AutoFit
End Sub

To specify the widths of many columns, access them using the Range class, then access the ColumnWidth property, and assign the desired value. Here is an example that sets the widths of Columns C, E, and H to 5 each:

Sub Exercise()
    Range("C:C, E:E, H:H").ColumnWidth = 5#
End Sub

To programmatically hide a column, first select it, then assign True to the Hidden property of the EntireColumn object of Selection. Consider the following code:

Private Sub Exercise()
    Columns("F:F").Select
    Selection.EntireColumn.Hidden = True
End Sub

This code will hide column F. 

To hide a range of columns, first select it as we reviewed already, then assign True to the Hidden property.

To reveal the hidden columns:

• Right-click any column header and click Unhide
• On the Ribbon, click Home. In the Cells section, click Format, position the mouse on Hide & Unhide, and click Unhide Columns

To unhide a hidden column, assign a False value to the Hidden property:

Private Sub Exercise()
    Columns("F:F").Select
    Selection.EntireColumn.Hidden = False
End Sub