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.

When Microsoft Excel starts, it displays the columns that have already been created. To support its group of columns, the Worksheet class is equipped with a property named Columns. There are various ways you can identify a column: using its index or using its label.

The columns on a worksheet are arranged by positions. A position is in fact referred to as the index of the column. The first column on the left has the index 1, the second from left has the index 2, and so on. Based on this, to refer to its 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

In the previous lesson, we saw that 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 codee can be written as follows:

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

This code now indicates that you are referring to the fourth column in the second worksheet. 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:

This means that if you trying accessing a column from a worksheet other than the one indicated in your code, the code would fail. If you want to access a specific column in any worksheet from the workbook that Microsoft Excel is currently showing, 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 programmatically 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, in the ch 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 G
    Range("D:H")
End Sub

Columns are refered to as non-adjacent when they do not follow each other. For example, columns B, D, and G are non-adjacent. 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 Column 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. Based on this, to select the fourth column using its indexed, 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 colection, 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 a new column, specify its successor using the Range class. 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