I can't access each cell of 'Columns(2)' unlike ''Range("B:B")' What kind of range is 'Columns(2)'?

Question

I want to get the whole second column in range("A1:B15"). Of course , I can use 'Range("B1:B15")' but the range may change according to the situations like different WorkSheets. So I need to get the range of the second column dynamically.

The following code I made first pops up an error(Runtime error 13: Type mismatch).

Sub test1()
    Dim r As Range

    For Each r In Range("A1:B15").Columns(2)
        Debug.Print r     '// Runtime Error '13': Type mismatch.
    Next r

End Sub

I thought that 'Columns(2)' is a kind of range, each cell of which can be accessed one by one. To my disappointment, here 'Range("A1:B15").Columns(2)' is not a normal series of range. Though, 'Range("A1:B15").Columns(2)' has an Address: '$B$1:$B:$15.' I couldn't understand this, at first.

To find out why the error occurs, I tried the following code.

Sub test2()
    Dim r As Range, TargetColumn As Range
    Set TargetColumn = Range("A1:B15").Columns(2)

    Debug.Print TargetColumn.Count  '//count is  1, not 15
    For Each r In TargetColumn
        Debug.Print r.Address       '//$B$1:$B$15
    Next r

End Sub

'TargetColumn.count' is not 15, but 1. 'r.address' is only "$B$1:$B$15.", not a series of "$B$1, $B$2, $B$3 ...." So 'Columns(2)' is not a normal series of range, but only a single range like a merged cells of range("$B$1:$B$15").

To bypass the type mismatch error, I changed the code like this:

Sub test3()
    Dim r As Range, TargetColumn As Range

    Set TargetColumn = Range("A1:B15").Columns(2)
    ' get the range using the address : Range("A1:B15")
    For Each r In Range(TargetColumn.Address)   
        Debug.Print r.Address, r.Value          ' works well
    Next r

End Sub

'Columns(2)' is not a normal series of range but a collection of range areas. It does have an address, which can be used in 'Range(Address).'

For example, 'Columns(1).address' is '$A$1:$A$15.' and 'Columns(2).address' is '$B$1:$B$15.' So the 'Columns' object is a collection of each columns. It is just like 'Union(range("$A$1:$A$15"), range("$B$1:$B$15"))'. Therefore, we can not access directly each cell in 'Columns(2).'

Is there an easier and simpler way to get a column range so that I can access each cell in the column range? I believe there must be a better way than 'Range(Range("A1:B15").Columns(2).address)' or 'Range("A1:B15").Columns(2)' which fails to run. 'Range("A1:B15").Columns(2).EntireRow' returns the whole range of "B:B" which I don't like to use.

That is a very well asked question. While it is different to the one I voted for as a duplicate target, I believe the accepted answer there covers your situation perfectly. — GSerg, Dec 25 '18 at 14:26
There is obviously tons of ways but here is a funny one if you wanna stick to columns: 'For Each r in Range(Range("A1:B15").Columns(2).Address)'. — VBasic2008, Dec 25 '18 at 14:42
Your first code only needs **.Cells** and **.Address** to work. — VBasic2008, Dec 25 '18 at 16:35

Dy.Lee · Accepted Answer · 2018-12-25T14:28:46.430

1

add Cells : Columns(2).Cells

Sub test2()
    Dim r As Range, TargetColumn As Range
    Set TargetColumn = Range("A1:B15").Columns(2).Cells

    Debug.Print TargetColumn.Count  '//count is  1, not 15
    For Each r In TargetColumn
        Debug.Print r.Address       '//$B$1:$B$15
    Next r

End Sub

debug result

edited Dec 25 '18 at 14:28

answered Dec 25 '18 at 14:21

Dy.Lee

7,527
1
12
14

1

'Range("A1:B15").Columns(2).Cells' is good enough. – konahn Dec 25 '18 at 14:34

score 1 · Answer 2 · answered Dec 25 '18 at 14:25

1

I am not sure what you want but maybe that's what you after

Sub test11()
    Dim r As Range

    For Each r In Range("A1:B15").Columns(2).Rows
        Debug.Print r    
    Next r

End Sub

This will loop through the single cells of column 2 and it will not run into a run time error as the line debug.print r will take the default item of the single cell which is value, r is still a range though.

answered Dec 25 '18 at 14:25

Storax

11,158
3
16
33

'Range("A1:B15").Columns(2).Rows' is also useful in this case. I didn't think of it. Thank you for the enlightenment. – konahn Dec 25 '18 at 14:37

score 0 · Answer 3 · answered Dec 25 '18 at 14:14

0

Perhaps using Resize & Offset will help:

Sub test1()
    Dim r As Range

    For Each r In Range("A1:B15").Resize(Range("A1:B15").Rows.Count, 1).Offset(0, 1)
        Debug.Print r.Address
    Next r

End Sub

answered Dec 25 '18 at 14:14

Justyna MK

3,523
3
11
25

Thank you for your quick answer. It works! But I think it's a little complicated. I want a still simpler one. There should be one, I suppose. – konahn Dec 25 '18 at 14:19
1

Although I haven't found a scenario where this code would trump the columns solutions, I think it is not correct to downvote inspiring working solutions like this one. – VBasic2008 Dec 25 '18 at 15:19

I can't access each cell of 'Columns(2)' unlike ''Range("B:B")' What kind of range is 'Columns(2)'?

3 Answers3