CODEWITHSUNDEEP
vbaexcel
AnneD
AnneD

Reputation: 11

Excel VBA - Dynamically supply ranges to Union method

User has defined named ranges to print in Excel.

I am reading these ranges into a VBA array. Is there a way to supply the range names to the Union method to set non-contiguous print ranges.

For example, something like: ActiveSheet.PageSetup.PrintArea = Union(Range(array(1)), Range(array(2))).Address

The number of ranges held in the array can vary. I've experimented with looping through the array and building a string variable, but no success.

Any help would be appreciated.

Upvotes: 1

Views: 1869

Answers (1)

David Zemens
David Zemens

Reputation: 53623

You'll have to substitute the actual range names or objects in the statement, but here is how to use the Union function to set a PrintArea:

Sub foo()
    Dim setup As PageSetup
    Set setup = ActiveSheet.PageSetup

    setup.PrintArea = Union(Range("MyRange1"), Range("MyRange2")).Address
End Sub

What I'm actually looking for is a method to construct the Union statement using range names that are held in an array

OK, then use the above method and a custom function to construct the Union in a loop:

Sub foo()
    Dim setup As PageSetup
    Dim RangeArray(1) As Range

    Set setup = ActiveSheet.PageSetup
    Set RangeArray(0) = Range("MyRange1")
    Set RangeArray(1) = Range("MyRange2")

    setup.PrintArea = GetUnion(RangeArray)
End Sub
Function GetUnion(arr As Variant) As String
    Dim itm As Variant
    Dim ret As Range
    For Each itm In arr
        If Not ret Is Nothing Then
            Set ret = Union(ret, itm)
        Else
            Set ret = itm
        End If
    Next
    If Not ret Is Nothing Then
        GetUnion = ret.Address
    Else
        GetUnion = ""  'May cause an error...
    End If
End Function

Upvotes: 3

Related Questions