CODEWITHSUNDEEP
vbaexcelrangeunion
jumpOnCommand
jumpOnCommand

Reputation: 107

VBA read union range horizontally

I'm trying to combine the values in a range of cells. I created a range as follows:

Dim rng As Range
Set rng = Application.Union(Range("A1:A3"), Range("C1:E2"))
For Each Address In rng
Debug.Print Address.Address
Next

I want to read the cells in horizontal order: A1,C1,D1,E1,A2,C2... etc. But instead they're being read A1,A2,A3,C1,C2,.. etc

How can I read them horizontally? Thanks

Upvotes: 2

Views: 1044

Answers (3)

Demetri
Demetri

Reputation: 859

Just thought this might be an interesting addition to Chris' solution? (I changed the range slightly to highlight some of the advantages)

Dim Rng As Range, Col As Variant, Rw As Variant

Set Rng = Application.Union(Range("A3:A5"), Range("C2:E4"))
For Each Rw In ActiveSheet.UsedRange.Rows
    For Each Col In Rw.Columns
        If Not Intersect(Col, Rng) Is Nothing Then Debug.Print Intersect(Col, Rng).Address
    Next Col
Next Rw

Upvotes: 0

user4039065
user4039065

Reputation:

You have to deal with the Range.Areas within the Range object created with the Union method.

Sub laterally()
    Dim r As Long, c As Long, a As Long
    Dim mnRW As Long, mxRW As Long, mnCL As Long, mxCL As Long
    Dim rng As Range

    With Worksheets("Sheet1")   '<~~ ALWAYS set the worksheet!
        Set rng = Union(.Range("A1:A3"), .Range("C1:E2"))
        Debug.Print rng.Address(0, 0)
        mnRW = Rows.Count: mxRW = 0
        mnCL = Columns.Count: mxCL = 0
        With rng
            For a = 1 To .Areas.Count
                With .Areas(a)
                    mnRW = Application.Min(mnRW, .Rows(1).Row)
                    mxRW = Application.Max(mxRW, .Rows(.Rows.Count).Row)
                    mnCL = Application.Min(mnCL, .Columns(1).Column)
                    mxCL = Application.Max(mxCL, .Columns(.Columns.Count).Column)
                End With
            Next a
            For r = mnRW To mxRW
                For c = mnCL To mxCL
                    If Not Intersect(.Cells, .Parent.Cells(r, c)) Is Nothing Then _
                        Debug.Print .Parent.Cells(r, c).Address(0, 0)
                Next c
            Next r
        End With
    End With
End Sub

After collecting the extents of the unioned range, each possible cell is looped through and the Intersect method is used to determine whether it belongs in the union or not.

Upvotes: 5

Chris Melville
Chris Melville

Reputation: 1518

A union of ranges doesn't care in which order the cells are added - whether it's by row or by column. So your original loop to unify the ranges is fine.

If you're concerned about the order in which you read the data, simply read by row or by column accordingly, with an inner and an outer loop. So for example, after you build the range, do as follows:

Dim Col as Variant, Rw as Variant
For Each Col in rng.Columns
    For each Rw in Col.Rows
        Debug.Print Rw.Address
    Next Rw
Next Col

Upvotes: 3

Related Questions