VBA Columns Automatically Merge?

Question

I am writing a program in VBA that first compiles a Range from a series of cells and then later loops through the range to collect the data in the cells.

The problem is that I need the range to maintain the order in which I add the cells so that I can then collect the data in the correct order when I go back through. If the data are in columns that are adjacent, then the range converts it into stacks of rows.

To see what I mean, If you run this program:

Sub test_function()

Dim My_Range As Range

Dim My_Cell As Variant

Dim i As Integer

i = 0

Set My_Range = Union(ActiveSheet.Range("A1:A5"), ActiveSheet.Range("B1:B5"))

For Each My_Cell In My_Range

i = i + 1

My_Cell.Value = i

Next My_Cell

End Sub

You can see that the range is compiled of two adjacent columns of data (A1:A5 and B1:B5), but instead of this EXPECTED output:

1 6

2 7

3 8

4 9

5 10

You get

1 2

3 4

5 6

7 8

9 10

It will reproduce this behavior even if you add one cell at a time using Set My_Range = ActiveSheet.Range("A1") Set My_Range = Union(My_Range, ActiveSheet.Range("A2")) Set My_Range = Union(My_Range, ActiveSheet.Range("A3")) etc...

Is there any way to preserve the order in which I add cells to a range? Or is the only way to have separate adjacent ranges? At the very least (if I can't get it to preserve the exact order) is there a way for it to do columns FIRST and THEN rows?

-Daniel

Answer 1

I would recommend building a collection of the ranges. You can then iterate through the collection, which will preserve the order in which the ranges were added. You can always union them later if you need a reference to the unioned range.

E.g.:-

(untested code)

Dim ranges As New Collection

ranges.Add(ranges.Count, ActiveSheet.Range("A1"))
ranges.Add(ranges.Count, ActiveSheet.Range("C6"))
// etc.

// then you can loop through the ranges in the order in which they were added
Dim rg As Range
For Each rg in ranges
    // do something with the range
Next rg

// you can also get a reference to the union if you want
Dim unionRange as Range
Set unionRange = ranges(0)
Dim rg2 As Range
For Each rg2 in ranges
    Set unionRange = Application.Union(unionRange, rg2)
Next rg2

// do something with the union