ActiveSheet.Columns?

Question

ActiveSheet.Columns.Address is $1:$65536 in XL2003.

ActiveSheet.Columns(2).Address is $B:$B.

ActiveSheet.Columns("b").Address is $B:$B.

ActiveSheet.Columns("b:h").Address is $B:$H.

First, why does Columns.Address return a range of rows instead of a range of columns (e.g. $A:$IV)?

Second, why is the syntax Columns(2), Columns("b") and Columns("b:h") valid?

The Columns help page does not indicate that Columns has any parameters.  However, the example does use Columns(1) , which is described as column one.

Nevertheless, I see no hint that Columns("b") and Columns("b:h") should be valid.

What is the complete acceptable syntax for ActiveSheet.Columns, and where is it explained in VBA help?

Answer 1

Well, I sure couldn't find it! Alas, no $64,000 for me :-)

Answer 2

The argument Columns accepts are:

• None: returns all columns.

Since Columns always returns all rows of the columns in its argument, Activesheet.columns returns all columns and hence all rows, which equals this address: 1:65536. Not logical, but it works.

• Single Numeric entry: the n-th column

Activesheet.Columns(1) returns column A

• A valid address string pointing to a complete (set of) column(s): The columns in question.

Answer 3

Second, why is the syntax Columns(2), Columns("b") and Columns("b:h") valid?

The Columns help page does not indicate that Columns has any parameters.  However, the example does use Columns(1) , which is described as column one.

Nevertheless, I see no hint that Columns("b") and Columns("b:h") should be valid.

Don't have access to my Winbox right now, but MacXL VBA Help, "Referring to rows and columns" topic:

  Columns(1)                         Column one

  Columns("A")   Column one

Answer 4

I wrote:

ActiveSheet.Columns.Address is $1:$65536 in XL2003.

ActiveSheet.Columns(2).Address is $B:$B.

ActiveSheet.Columns("b").Address is $B:$B.

ActiveSheet.Columns("b:h").Address is $B:$H.

Given that, does the following seem unexpected?

If r is set to Range("b2"):

• r.Columns(2).Address is $C$2 and r.Columns(2).Count is 1.
• and r.Columns(2) = 2 sets only C2 to 2.
• whereas r.Columns(2).EntireColumn = 3 sets all of C:C to 3 starting in C1.
• yet r.Columns(2).AutoFit works, whereas r.AutoFit results in an error and r.EntireColumn.AutoFit is required as expected.

It seems as though range.Columns is sometimes treated as a single cell and sometimes treated as the entire column.

FYI, VBA help describes Columns applied to Range objects as:   "Returns a Range object that represents the columns  in the specified range".

Color me confused!  Can anyone offer a cohesive explanation?

Answer 5

Jan wrote:

The argument Columns accepts are:

[....]

Thanks for confirming what I had learned empirically.

Now the $64,000 question:  "where is it explained in VBA help?".

I wonder if this has something to do with general syntax for collections, something I am not very familiar with.

For example, I learned by observation that is r is Range variable set to Range("a1:a100") or Range("a1:z100"), r(i) can be used to index the collection.  It seems especially useful when r is one column or row, since it obviates the need to r.Offset or r.Cells.

(I discovered that for 2-dimensional ranges, r(i) indexes across, then down.)