The Columns of a Worksheet

Columns Fundamentals

In Microsoft Excel, a worksheet is arranged in vertical lists or series. These make it possible to create an organized list of items. Each one of these vertical arrangements is called a column. For this reason, we can say that a worksheet is primarily made of columns.

In VBA for Microsoft Excel, to programmatically refer to a column, you will use a collection. In your code, one of the classes you can use to access a column is named Range. As we will see in various examples, you can directly access this class. 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.

When Microsoft Excel starts, it displays the columns that have already been created. To identify a column, each displays a small box in the top section: this is the column header. Each column header displays one or more letters:

As seen above, these are also referred to as the columns of the worksheet. 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.

Besides the index, a column also has a name. The name of a column displays in the column header. The name uses one, two, or three letters. The most left column is called, and is labeled, A, and its index is 1 as we saw above. The second has a label of B and its index is 2, and so on.

A Microsoft Excel worksheet contains 16,384 columns going from Column A to Column XFD:

When you start a workbook in Microsoft Excel, the application makes all these columns available. You can use all of them or just a few, but they are always available.

Among the various ways you can use a column, we will see in various sections that you can click it or use the keyboard to get to a column. You can also right-click a column. When you do, an expanded menu would appear:

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

Columns are refered to as adjacent when they are next to each other, or when they follow each other. For example, columns B, C, and D are adjacent. Also, Columns Y, Z, AA, AB, and AC are adjacent.

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

At times, you will almost want to alter the display of a column or various columns. You have to select that column or the group of columns first. Another reason you may need to select a column or a group of columns is because you would need to take some action on it.

You can select a column or a group of columns using the mouse, the keyboard, or a combination of both:

• To select a column using the mouse, click its column header:
   
  
   
  
• To select a column using the keyboard, click anything under it, then press and hold Ctrl. While Ctrl is down, press the Space bar and release Ctrl.

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 inxed, 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.

You can also select more than one column. Selecting columns in a range consists of selecting adjacent columns. To perform this type of selection, you can use the mouse, a combination of the mouse and the keyboard, or code.

To select columns in a range using the mouse, click one column header and hold the mouse down. Then drag in the direction of the range

To select a range of columns using the mouse and the keyword, click one column at one end of the desired range. Press and hold Shift. Then click the column at the other end, and release the mouse.

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

Random selection consists of selecting columns that are not adjacent. For example, this allows you to select columns B, D, and H. To do this, click one column header, press and hold Ctrl. Then click each desired column header. When you have selected the desired columns, release the mouse.

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.

As mentioned already, Microsoft Excel has columns named from A to XFD with a maximum of 16384. Microsoft Excel allows you to add a column. Actually, you can insert a column on the left side of an existing column. When you do, Microsoft Excel internally removes the very last column to keep the count to 16384.

To visually add a new column:

• Right-click the column header of the column that will be on the right side of the new column you want to create, and click Insert
• Click the column header or any box under  it. On the Ribbon, click Home. In the Cells section, click the arrow under Insert and click Insert Sheet 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 add more than one column, first select the columns, whether in a range or randomly. Then:

• Right-click one of the columns (whether one of the column headers or a box of one of the selected columns) that will be on the right side of the new columns you want to create, and click Insert
• (After selecting the columns,) On the Ribbon, click Home. In the Cells section, click the arrow button Insert and click Insert Sheet Columns

If you select columns randomly (non-adjacent), a new column would be created on the left side of each of the selected columns.

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

If you find out that you have a column you do not want, you can remove it. To remove a column:

• Right-click the column header and click Delete
• Click the column header or any box under  it. On the Ribbon, click Home. In the Cells section, click Delete and click Delete Sheet Columns

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 delete more than one column, first select the columns, whether in a range or randomly. Then:

• Right-click one of the columns (whether one of the column headers or a box of one of the selected columns) and click Delete
• (After selecting the columns,) On the Ribbon, click Home. In the Cells section, click Delete and click Delete Sheet Columns

If no box under the column header has anything, you would not receive a warning and the column would simply be removed. If at least one of the boxes under the column header has a value, you may receive a warning to indicate whether you want to continue with the operation or not.

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 display the information under it, a column uses a measure from its left border to its right border. This measure is referred to as its width. By default, when Microsoft Excel comes up, all columns use the same width. You are allowed to change the width of one column or a group of columns.

There are various techniques you can use to change the width of a column. You can manually resize a column or a group of columns, or you can use a dialog box to exercise more control.

To manually resize a column, position the mouse on the short line that separates a column header from its right neighbor. Here is an example:

Click, then drag left or right until the small box displays the width you desire, then release the mouse.

You can also resize a group of columns. First, select the columns you want to work on. Then position the mouse on the column header border of one of the selected columns. Click and drag left or right in the direction of your choice until you get the desired with. Then release the mouse.

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

If one of the boxes under a column header displays the width you want, you can resize the column to the content of that box. To do this, click the box that has the desired width. Then:

• Double-click the short line that separates the column header from the column on its right (if you are on the most right column, that will be the line that serves as the column's right border)
• On the Ribbon, click Home. In the Cells section, click Format and click AutoFit Column Width

In the same way, to set the widths of columns based on some boxes under their columns headers, select those boxes (in Lesson 4, we will learn how to select the boxes). Then:

• Double-click the short line on one side of the column headers
• On the Ribbon, click Home. In the Cells section, click Format and click AutoFit Column Width

To undo any of these actions:

• On the Quick Access toolbar, click the Undo button

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

You can use a dialog box to set exactly the desired width of a column or a group of columns. To specify the width of a column:

• Right-click the column header and click Column Width...
• Click a column header or any box under it. Then, on the Ribbon, click Home. In the Cells section, click Format and click Column Width...

To specify the same width for many columns:

• Select a range of columns. Right-click one of the columns (right-click either one of the column headers or inside the selection) and click Column Width...
• Randomly select a group of (non-adjacent) columns. Right-click one of the column headers and click Column Width...
• Select the columns, whether in a range or randomly (non-adjacent). On the Ribbon, click Home. In the Cells section, click Format and click Column Width...

Any of these actions would open the Column Width dialog box. From there, accept or enter the desired value and click OK

To undo any of these actions:

• On the Quick Access toolbar, click the Undo button
• Press Ctrl + Z

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

You may have noticed that, in the workbooks we have used so far, there are some values under some column headers. Because a column is primarily a group of values, you can copy its values to the clipboard and put them in another column.

In our introduction, we saw that columns assume some default positions when Microsoft Excel starts. On a normal computer spreadsheet, you can move a column from its current position to another.

To move a column, first click its column header to select it. Position the mouse on one of the vertical lines of the selected column:

Click and hold your mouse down. Drag left or right. Two vertical lines would guide you. When you get the column to the desired location, release the mouse.

When you move a column, its boxes move but it assumes the lettered name of the new location so the names would still follow the alphabetic sequence.

To move a group of columns, select them. Position the mouse on one of the vertical lines of the selection:

Click and hold your mouse down. Drag left or right. Vertical lines would guide you. When you get the columns to the desired location, release the mouse. When you move the columns, their boxes move but they assume the lettered name of the new location with the appropriate alphabetic sequence.

As mentioned already, when moving one or more columns, their location changes. In some cases, you may not want to move the column(s) but only its(their) content. To support this, the operating system provides the clipboard and Microsoft Excel has a high level of support for it. In other words, you can copy the contents of column(s) to the clipboard and paste it(them) to other column(s).

To copy the contents of a column to the clipboard:

• Right-click the column header and click Copy
• Click the column header. On the Ribbon, click Home. In the Clipboard section, click Copy

After copying a column to the clipboard, all of its values are made available. To put those values on another column:

• Right-click the target column header and click Paste
• Click the column header. On the Ribbon, click Home. In the Clipboard section, click Paste

As seen already, if you move one or more columns, they go with their contents. If you copy the contents of columns, you would have duplicate (contents) of columns. As an alternative, you can move only the values of columns, not the columns themselves. The Microsoft Windows operating system supports this operation through cutting to the clipboard.

To temporarily move the contents of a column to the clipboard to wait to be pasted:

• Right-click the column header and click Cut
• Click the column header. On the Ribbon, click Home. In the Clipboard section, click Cut

After cutting a column to the clipboard, if you do not want to paste it anywhere, you can press Esc. If you want to paste it to another column:

• Right-click the target column header and click Paste
• Click the column header. On the Ribbon, click Home. In the Clipboard section, click Paste

To temporarily move the contents of many columns to the clipboard to wait to be pasted, select the columns. Then:

• Right-click either one of the column headers or inside the selection, and click Cut
• On the Ribbon, click Home. In the Clipboard section, click Cut

If you want to paste the values to another group of columns:

• Right-click a target column header and click Paste
• Click a column header. On the Ribbon, click Home. In the Clipboard section, click Paste

When you paste, the values of the boxes under the original columns would be emptied.

When working on a list, you don’t always need all columns displaying all the time. You can hide a column whose presence is not required at a particular time. In Microsoft Excel, you can hide one or more columns.

To hide one column:

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

When a column has been hidden, its letter disappears from the sequence and the line between the previous neighbors is thicker than the other dividing lines:

To hide many columns, select the columns. Then:

• Right-click one of the column headers or inside the selection and click Hide
• On the Ribbon, click Home. In the Cells section, click Format, position the mouse on Hide & Unhide, and click Hide Columns

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

If you have a list wider than the Microsoft Excel area can show, you can scroll to the right to see hidden columns. While you are scrolling to the right, some columns would be disappearing from the left section of the Microsoft Excel interface. If you want, you can freeze a column so that, when you scroll to the right, a column or some columns would be fixed and would not move. Also, the column(s) from the left of the frozen column would not move either.

To freeze a column, click the column header of the column that will lead the moving columns. On the Ribbon, click View. In the Window section, click Freeze Panes, and click Freeze Panes.

An alternative to freezing is to split the group of columns into two sections. Just as done for the freezing, you can choose a column to use as reference and scroll the columns from its side. To split the group of columns in two, click a column header. On the ribbon, click View. In the Window section, click Split. This would display a bar:

The similarities between the freezing and splitting are as follows:

• The columns are divided in two groups
• The user can scroll the columns from the right side of the divider

The differences between the freezing and splitting are as follows:

• If you freeze a column, you can scroll the columns on the right side of the frozen line but you cannot scroll the columns from the left side of the frozen line. If you split the columns, you can scroll the columns from either the left or the right side of the splitting bar
• If you freeze a column, you cannot move the freezing line to another column (you would have to unfreeze the column, then re-freeze). If you split the columns, you can move the splitting bar to the left or the right
• If you freeze a column, to remove the frozen line, you use the Ribbon. If you split the columns, to remove the splitting line, you can double-click it or, in the Window section of the View tab of the Ribbon, you can click the Split button