[vba] dynamic printarea

[Antman404]

Hello again
i got this code
y1.PageSetup.PrintArea = Union(PA(1), PA(2), PA(3), PA(4), PA(5), PA(6), PA(7)).Address

problem is, if one or more of PA(#) contain no range, the union gives an error

now i need something to exclude the empty PA(#)'s.
i tried the following

Dim PA As Object
Set PA = CreateObject("Scripting.Dictionary")

...CODE WHERE PA(#) GETS A RANGE OR NOT...

For v = 1 To 6

If PA(v) Is Not Nothing Then

y1.PageSetup.PrintArea = Union(PA(v - 1), PA(v)).Address

End If

Next v

But it does only work with the last two PA(#).

PA(1) and PA(2) will always have a Range, so there will be at no time all of them empty

 

[GWteB]

Perhaps something like this will work for you ...

    Dim PA As Object
    Set PA = CreateObject("Scripting.Dictionary")

    ...CODE WHERE PA(#) GETS A RANGE OR NOT...

    Dim r As Range
    
    For v = 0 To 5
        If Not PA(v) Is Nothing Then
            If r Is Nothing Then
                Set r = PA(v)
            Else
                Set r = Union(r, PA(v))
            End If
        End If
    Next v
    
    y1.PageSetup.PrintArea = r.Address

 

[Antman404]

Thank you! It works.

 

[GWteB]

You are welcome and thanks for letting me know.