PDA

View Full Version : [SOLVED] Macro for setting up a printarea of random large quntaties of ranges



taralej
10-23-2015, 08:30 AM
Hello,

I have developed a macro that under certain conditions marks a range for printing. Ranges are spread randomly in active sheet.
I save my ranges as strings in arrays.
After that I place all the strings from arrays in one variable myrngTEXT
Let me give you an output of myrngTEXT -

$P$225:$AC$249,$P$281:$AC$305,$P$253:$AC$277,$P$309:$AC$333,$P$393:$AC$417,
$P$449:$AC$473,$P$421:$AC$445,$P$477:$AC$501,$AE$393:$AR$417,$AT$393:$BG$41 7,$AE$421:$AR$445,$AT$421:$BG$445,$AT$505:$BG$529,
$AT$561:$BG$585,$AT$533:$BG$557,$AT$589:$BG$613,$AT$800:$BG$850

Currently I am facing this problem when the number of ranges exceeds this point excel throws me this - 1004 Run time error Unable to set the PrintArea property of the PageSetup class.

How can I escape from this, it seems it has a maximum amount of characters?!
Just sharing a bit of the code, my criteria of ranges is in no way important and how I get them. The important thing is
that they are randomly spread all over active sheet and I can't lower their number!



ReDim Preserve temp(count - 1)
myrngTEXT = temp(0)

For i = 1 To UBound(temp)
myrngTEXT = myrngTEXT & "," & temp(i) 'saving all the strings from array in one string
Next

ActiveSheet.PageSetup.PrintArea = myrngTEXT 'It crashes here !!!

taralej
10-24-2015, 12:20 AM
Also I tried recording a macro while selecting more ranges it still refuses to add them in the recorded macro if they exceed the threshold of characters. However it still marks them for printing.
I am stuck! Please help.

taralej
10-24-2015, 04:12 AM
Solved.
1) Assign a small number of strings from array to string
2) Print them out
3) Clear string
4) Go to 1) with loop

Do While i <= UBound(temp)
Do While j < limit
If j + grab > UBound(temp) Then Exit Do
If myrngTEXT = vbNullString Then
myrngTEXT = temp(j + grab)
j = j + 1
Else
myrngTEXT = myrngTEXT & "," & temp(j + grab)
j = j + 1
End If
Loop
grab = grab + j
j = 0
ActiveSheet.PageSetup.PrintArea = myrngTEXT
Set PrintRange = Range(myrngTEXT)
MSG1 = MsgBox("Искате ли да принтирам командировки?", vbYesNo, "Командировки")
If MSG1 = vbYes Then
PrintRange.PrintOut
myrngTEXT = vbNullString
i = i + limit
Else
Exit Sub
End If


Loop