Union and non-contiguos range

[Comfy]

I'm losing my mind try to work out why this is doing what it is doing:

For i = 30 To 30
        Set rng = Application.Union(Cells(i, 8), Cells(i, 12), Cells(i, 14), Cells(i, 16), Cells(i, 18))
        MsgBox rng(1)
        MsgBox rng(2)
        MsgBox rng(3)
        MsgBox rng(4)
        MsgBox rng(5)

Instead of this returning 1,2,3,4,5

Row 30 values

it's returning 1,a,b,c,d

Column 8 Values.


<b>Excel 2007</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>H</th><th>I</th><th>J</th><th>K</th><th>L</th><th>M</th><th>N</th><th>O</th><th>P</th><th>Q</th><th>R</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">30</td><td style="text-align: right;;">1</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">2</td><td style="text-align: right;;"></td><td style="text-align: right;;">3</td><td style="text-align: right;;"></td><td style="text-align: right;;">4</td><td style="text-align: right;;"></td><td style="text-align: right;;">5</td></tr><tr ><td style="color: #161120;text-align: center;">31</td><td style=";">a</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">32</td><td style=";">b</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">33</td><td style=";">c</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">34</td><td style=";">d</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Sheet1</p><br /><br />

 

[VoG]

Possibly (untested)

Set Rng = Application.Transpose(Union(Cells(i, 8), Cells(i, 12), Cells(i, 14), Cells(i, 16), Cells(i, 18)))

 

[Comfy]

Thanks VoG I will test later.

Have been an absolute numb nuts and close the wb I was working on without saving.

Noooooooooooo

 

[Comfy]

Possibly (untested)

Set Rng = Application.Transpose(Union(Cells(i, 8), Cells(i, 12), Cells(i, 14), Cells(i, 16), Cells(i, 18)))

Object Required.

It appears as though Transpose is not valid when Setting a Range

 

[Comfy]

After some more testing don't think I can use this.

I will have to find another way of looping through a non-contiguos range like:

Set rng = Union(Cells(i, 8), Cells(i, 12), Cells(i, 14), Cells(i, 16), Cells(i, 18))
For Each rng1 In rng.Areas
MsgBox rng1.Value
Next

But I will need to set which rng1 to start in. As I will not want to start at the first each time

 

[Richard Schollar]

You don't need to use the Areas there - you can just use

For Each rng1 In rng
MsgBox rng1.Value
'blah, blah
Next rng1

 

[Comfy]

ok Ta.

 

[Comfy]

I've managed to do what I wanted to do, it's not pretty but it'll do