CODEWITHSUNDEEP
vbaexcelrangeunion
Cloud
Cloud

Reputation: 19333

Create a 2D array from disjoint columns

I have a large Excel VBA macro (which cannot be modified) that accepts a range as input. It currently runs on large ranges via:

CustomFunc(Range("A10:I110"), outputFileName)

Now, I need to change a few calls this function to use a different column in place of column A which has a very different set of strings. I've tried to combine columns in the following ways, but not of them seem to work (function is complaining about incompatible types, so I think that the concatenation of columns I'm attempting isn't working):

CustomFunc(Range("X10:X110,B10:I110"), outputFileName)
CustomFunc(Union(Range("X10:X110"), Range("B10:I110")), outputFileName)

I've also tried a function that is supposed to help me accomplish this via VBA, but the result is a variant rather than a range, so I can't use it to generate the input to CustomFunc.

How can I generate a concatenated set of columns and present it as a range to my function CustomFunc?

Edit

It appears that the Union function disregards order.

NEW QUESTION:

How can I create an array from two ranges, i.e. X10:X110 and B10:I110 to create a 9x100 array of items, with the X column being the first/left-most column?

Upvotes: 1

Views: 407

Answers (1)

DecimalTurn
DecimalTurn

Reputation: 4127

If you use the Union object on non-continuous column ranges and passing it to a function or sub procedure, looping through the rows might give you something different than what you expect. It's almost like VBA considers the two columns to be stacked.

For example, if you run the macro test in the code below, you will see that looping though the rows means that you are looping though each cells of the 2 columns.

Sub test()
    Call SelectRowByRow(Union(Range("A1:A3"), Range("C1:C3")))
End Sub

Sub SelectRowByRow(rng As Range)

    Dim r As Range, i As Integer
    For Each r In rng.Rows
        r.Select
        i = i + 1
        MsgBox "Row " & i
    Next

End Sub

This gets a little messy if some columns are contiguous, but not all. For example, with test2, you'll see that column C and D are consider as a seperate block from column A, but it will consider their rows together.

Sub test2()
    Call SelectRowByRow(Union(Range("A1:A3"), Range("C1:C3"), Range("D1:D3")))
End Sub

Because of this, I would suggest to loop through columns instead.

NEW QUESTION:

If you only have 2 ranges, you could use a function like this:

Function UnionOrdered(rng1 As Range, rng2 As Range) As Range

    Dim wksTemp As Worksheet
    On Error Resume Next
        Set wksTemp = Sheets("TempUnion")
    On Error GoTo 0

    If wksTemp Is Nothing Then
        Set wksTemp = Sheets.Add
        wksTemp.Name = "TempUnion"
        wksTemp.Visible = False
    End If

    wksTemp.Cells.Clear
    rng1.Parent.Activate
    wksTemp.Cells(1, 1).Resize(rng1.Rows.Count, rng1.Columns.Count).Value2 = rng1.Value2
    wksTemp.Cells(1, 1 + rng1.Columns.Count).Resize(rng2.Rows.Count, rng2.Columns.Count).Value2 = rng2.Value2

    Set UnionOrdered = wksTemp.Range("A1").CurrentRegion

End Function

It will make use of a hidden sheet to copy the content of the specified range in order to select the range in the right order.

Upvotes: 1

Related Questions