Column Columns Row and Rows Properties

The Excel object model does not have an official Columns or Rows collection. However, the Columns property does return a collection of Range objects, each of which represents a column. Thus:

ActiveSheet.Columns(i)

is the Range object that refers to the ith column of the active worksheet (and is a collection of the cells in that column). Similarly:

ActiveSheet.Rows(i)

refers to the ith row of the active worksheet.

The Columns and Rows properties can also be used with a Range object. Perhaps the simplest way to think of rng.Columns is as the collection of all columns in the worksheet reindexedso that column 1 is the leftmost column that intersects the range rng. To support this statement, consider the following code, whose results are shown in Figure 19-1:

Dim i As Integer Dim rng As Range

Set rng = Range("D1:E1, G1:I1") rng.Select

MsgBox "First column in range is " & rng.Column ' Displays 4

MsgBox "Column count is " & rng.Columns.Count ' Displays 2

Figure 19-1. A noncontiguous range

Figure 19-1. A noncontiguous range

Note that the range rng is selected in Figure 19-1 (and includes cell D1). The Column property of a Range object returns the leftmost column that intersects the range. (Similarly, the Row property returns the topmost row that intersects the range.) Hence, the first message box will display the number 4.

Now, from the point of view of rng, Columns(1) is column number 4 of the worksheet (column D). Hence, Columns(0) is column number 3 of the worksheet (column C) which, incidentally, is not part of rng. Indeed, the first column of the worksheet is column number

which is precisely why we started the For loop at this value.

Next, observe that:

rng.Columns.Count is equal to 2 (which is the number displayed by the second message box). This is a bit unexpected. However, for some reason, Microsoft designed the Count property of r ng.Columns to return the number of columns that intersect only the leftmost area in the range, which is area D1:E1. (We will discuss areas a bit later.) Finally, note that:

rng.Columns(3)

is column F, which does not intersect the range at all.

As another illustration, consider the range selected in Figure 19-2. This range is the union B4:C5, E2:E7.

Figure 19-2. The range as a union

Figure 19-2. The range as a union

The code:

Dim rng As Range

MsgBox rng.Columns(1).Cells(1, 1).Value displays a message box containing the x shown in cell B4 in Figure 19-2 because the indexes in the Cells property are taken relative to the upper cell in the leftmost area in the range.

Note that we can use either integers or characters (in quotes) to denote a column, as in:

Columns(5) and:

Columns("E")

We can also write, for instance:

Columns("A:D")

to denote columns A through D. Similarly, we can denote multiple rows as in:

Rows("1:3") Since a syntax such as: Columns("C:D", "G:H")

does not work, the Union method is often useful in connection with the Columns and Rows methods. For instance, the code:

Dim rng As Range

Set rng = Union(Rows(3), Rows(5), Rows(7)) rng.Select selects the third, fifth, and seventh rows of the worksheet containing this code or of the active worksheet if this code is in a workbook or standard code module.

0 -2

Responses

  • kristian
    How to select the whole column in macros?
    7 years ago

Post a comment