I am attempting to create a macro to dynamically set a range based on user input.

The spreadsheet is based on 13 yearly periods.

I have sucessfully set the ranges and manually entered the selected ranges using the set Multirange = Union(P1,P2,P3....)

This works

However when I created a for next loop to build the union string based on starting and ending periods , I get a type mismatch

I know I need an object/range type because that is what is created when I enter it manually. The result of the for next loop is
"Union(P1,P2,P3,.....)"

The quotes are probably what is genereating the error.

Is there an easy way to convert the string to a range ?

I have tried just assigning the string to a range/object variable.
That does not work.

Here is the complete macro code. User input part is not done yet.

Multirange is the variable that needs to get the result of bunion and be set to a range.


Sub PrintRange()
Dim P1, P2, P3, P4, P5, P6, P7, P8, P9, P10, P11, P12, P13 As Range

Dim Multirange
Dim Pstart, Pend
Dim Multi, Period
Dim bunion





 With ActiveSheet.PageSetup
        .PrintTitleRows = "$7:$10"
        .PrintTitleColumns = "$C:$C"
    End With
    ActiveSheet.PageSetup.PrintArea = "$AU$141:$CF$157"
    With ActiveSheet.PageSetup
        .LeftHeader = ""
        .CenterHeader = ""
        .RightHeader = ""
        .LeftFooter = ""
        .CenterFooter = ""
        .RightFooter = ""
        .LeftMargin = Application.InchesToPoints(0.25)
        .RightMargin = Application.InchesToPoints(0.25)
        .TopMargin = Application.InchesToPoints(0.5)
        .BottomMargin = Application.InchesToPoints(0.25)
        .HeaderMargin = Application.InchesToPoints(0.5)
        .FooterMargin = Application.InchesToPoints(0.5)
        .PrintHeadings = True
        .PrintGridlines = False
        .PrintComments = xlPrintNoComments
        .PrintQuality = 600
        .CenterHorizontally = False
        .CenterVertically = False
        .Orientation = xlLandscape
        .Draft = False
        .PaperSize = xlPaperLetter
        .FirstPageNumber = xlAutomatic
        .Order = xlDownThenOver
        .BlackAndWhite = True
        .Zoom = 74
        .PrintErrors = xlPrintErrorsBlank

    End With


Pstart = 1
Pend = 13
 bunion = "Union" & "("

'build Union string

    For x = Pstart To Pend

            Period = "P" & x

                If x = Pend Then
                Multi = Period
                Else
                 Multi = Period & "," & " "

                 End If

            bunion = bunion + Multi

             If x = Pend Then
             bunion = bunion & ")"
             End If

    Next
    MsgBox bunion

   'COMBINE SELECTED RANGES INTO ONE. MUST BE CONTIGUOUS


Set P1 = Sheets("2005").Range("E12:J157") 'SELECT A PERIOD RANGE INCLUDE HIDDEN COLUMNS

Set P2 = Sheets("2005").Range("K12:P157")

Set P3 = Sheets("2005").Range("Q12:V157")

Set P4 = Sheets("2005").Range("W12:AB157")

Set P5 = Sheets("2005").Range("AC12:AH157")

Set P6 = Sheets("2005").Range("AI12:AN157")

Set P7 = Sheets("2005").Range("AO12:AT157")

Set P8 = Sheets("2005").Range("AU12:AZ157")

Set P9 = Sheets("2005").Range("BA12:BF157")

Set P10 = Sheets("2005").Range("BG12:BL157")

Set P11 = Sheets("2005").Range("BM12:BR157")

Set P12 = Sheets("2005").Range("BS12:BX157")

Set P13 = Sheets("2005").Range("BY12:BZ157")


Set Multirange = bunion


Multirange.Select

Selection.PrintOut Copies:=1, Preview:=True, Collate:=True

End Sub


Pablo

I could not make this work no matter what I tried.

I came up with a better solution though, I decided it was easier to hide the columns I did not want to print instead of selecting the range that I wanted.

Here is the code I used if anyone is intertested.


Option Base 1

Sub PrintRange()
Dim Pstart, Pend
Dim All As Range
Dim P1 As Range

Dim Multirange
Dim brange
Dim x

'Set column Ranges


Set P1 = Sheets("2005").Columns("E:F")
Set P2 = Sheets("2005").Columns("K:L")
Set P3 = Sheets("2005").Columns("Q:R")
Set P4 = Sheets("2005").Columns("W:X")
Set P5 = Sheets("2005").Columns("AC:AD")
Set P6 = Sheets("2005").Columns("AI:AJ")
Set P7 = Sheets("2005").Columns("AO:AP")
Set P8 = Sheets("2005").Columns("AU:AV")
Set P9 = Sheets("2005").Columns("BA:BB")
Set P10 = Sheets("2005").Columns("BG:BH")
Set P11 = Sheets("2005").Columns("BM:BN")
Set P12 = Sheets("2005").Columns("BS:BT")
Set P13 = Sheets("2005").Columns("BY:BZ")

'Place ranges in an array

brange = Array(P1, P2, P3, P4, P5, P6, P7, P8, P9, P10, P11, P12, P13)

'Get input from user
'Start Period
Pstart = Application.InputBox(prompt:="Enter Start Period to Print", Type:=1)

If Pstart < 1 Or Pstart > 13 Then
MsgBox "Invalid input"
Exit Sub
End If


If Pstart = "" Then
Exit Sub
End If

'End Period
Pend = Application.InputBox(prompt:="Enter End Period to Print", Type:=1)

If Pend < 1 Or Pend > 13 Then
MsgBox "Invalid input"
Exit Sub
End If



If Pend = "" Then
Exit Sub
End If

For x = 1 To Pstart - 1
    brange(x).Hidden = True
Next

For x = Pend + 1 To 13
    brange(x).Hidden = True

Next




'SELECT Print Range


Set All = Sheets("2005").Range("E12:CF157")

All.Select





'Print Selected Range

Selection.PrintOut Copies:=1, Preview:=True, Collate:=True

Works like a Champ !


Pablo