ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Programming (https://www.excelbanter.com/excel-programming/)
-   -   Long print ranges in VBA (https://www.excelbanter.com/excel-programming/422864-long-print-ranges-vba.html)

Tom Joseph
Long print ranges in VBA
 
I am trying to get VBA to set a long print range. I have been able to get
this to work and set up printing for 22 reports. I would like it to be able
to work for 23-24 reports, but it appears I dont have the VBA
continuation/wrapping code right or it is a VBA limitataion.

I have commented out the 23 and 24 report options, after a number of
attempts at using some kind of continuation syntax.

Can someone please help with this? If this cannot be directly coded,
perhaps there is a workaround.

Thanks.


If n = 22 Then
'Studies = 22
Sheets("Report").Select
Range( _

"C36:U119,C236:U319,C436:U519,C636:U719,C836:U919, C1036:U1119,C1236:U1319,C1436:U1519,C1636:U1719,C1 836:U1919,C2036:U2119,C2236:U2319,C2436:U2519,C263 6:U2719,C2836:U2919,C3036:U3119,C3236:U3319,C3436: U3519,C3636:U3719,C3836:U3919,C4036:U4119,C4236:U4 319" _
).Select
ActiveSheet.PageSetup.PrintArea = _

"C36:U119,C236:U319,C436:U519,C636:U719,C836:U919, C1036:U1119,C1236:U1319,C1436:U1519,C1636:U1719,C1 836:U1919,C2036:U2119,C2236:U2319,C2436:U2519,C263 6:U2719,C2836:U2919,C3036:U3119,C3236:U3319,C3436: U3519,C3636:U3719,C3836:U3919,C4036:U4119,C4236:U4 319"
End If

'If n = 23 Then
'Studies = 23
' Sheets("Report").Select
' Range( _
'
"C36:U119,C236:U319,C436:U519,C636:U719,C836:U919, C1036:U1119,C1236:U1319,C1436:U1519,C1636:U1719,C1 836:U1919,C2036:U2119,C2236:U2319,C2436:U2519,C263 6:U2719,C2836:U2919,C3036:U3119,C3236:U3319,C3436: U3519,C3636:U3719,C3836:U3919,C4036:U4119,C4236:U4 319,C4436:U4519" _
' ).Select
' ActiveSheet.PageSetup.PrintArea = _
'
"C36:U119,C236:U319,C436:U519,C636:U719,C836:U919, C1036:U1119,C1236:U1319,C1436:U1519,C1636:U1719,C1 836:U1919,C2036:U2119,C2236:U2319,C2436:U2519,C263 6:U2719,C2836:U2919,C3036:U3119,C3236:U3319,C3436: U3519,C3636:U3719,C3836:U3919,C4036:U4119,C4236:U4 319,C4436:U4519"
'End If

'If n = 24 Then
'Studies = 24
' Sheets("Report").Select
' Range( _
'
"C36:U119,C236:U319,C436:U519,C636:U719,C836:U919, C1036:U1119,C1236:U1319,C1436:U1519,C1636:U1719,C1 836:U1919,C2036:U2119,C2236:U2319,C2436:U2519,C263 6:U2719,C2836:U2919,C3036:U3119,C3236:U3319,C3436: U3519,C3636:U3719,C3836:U3919,C4036:U4119,C4236:U4 319,C4436:U4519,C4636:U4719" _
' ).Select
' ActiveSheet.PageSetup.PrintArea = _
'
"C36:U119,C236:U319,C436:U519,C636:U719,C836:U919, C1036:U1119,C1236:U1319,C1436:U1519,C1636:U1719,C1 836:U1919,C2036:U2119,C2236:U2319,C2436:U2519,C263 6:U2719,C2836:U2919,C3036:U3119,C3236:U3319,C3436: U3519,C3636:U3719,C3836:U3919,C4036:U4119,C4236:U4 319,C4436:U4519,C4636:U4719"
'End If

Jim Cone[_2_]
Long print ranges in VBA
 

My guess is areas 23 and 24 are failing because they exceed 255
characters in length.
255 characters is a common limitation on the length of text.
Area 22 is 252 characters in length while 23 and 24 are 264 and 276.
As a test try whacking off 25 characters from the end of each and
see if it flies.
Also, you do not have to select a range to set a print area...
'--
'Studies = 22
ActiveSheet.PageSetup.PrintArea = _
Range("C36:U119,C236:U319,C436:U519,C636:U719,C836 :U919,C1036:U1119,C1236:U1319," & _
"C1436:U1519,C1636:U1719,C1836:U1919,C2036:U2119,C 2236:U2319,C2436:U2519," & _
"C2636:U2719,C2836:U2919,C3036:U3119,C3236:U3319,C 3436:U3519,C3636:U3719," & _
"C3836:U3919,C4036:U4119,C4236:U4319").Address

--
Jim Cone
Portland, Oregon USA



"Tom Joseph"

wrote in message
I am trying to get VBA to set a long print range. I have been able to get
this to work and set up printing for 22 reports. I would like it to be able
to work for 23-24 reports, but it appears I dont have the VBA
continuation/wrapping code right or it is a VBA limitataion.
I have commented out the 23 and 24 report options, after a number of
attempts at using some kind of continuation syntax.
Can someone please help with this? If this cannot be directly coded,
perhaps there is a workaround.
Thanks.
-snip-

Tom Joseph
Long print ranges in VBA
 
Thanks for the note, Jim.

Can you elaborate on seting a print area without selecting a range (in VBA)?
Perhaps a brief example will help me get started.

These are 24 noncontigous ranges.

Thanks,

Tom

"Jim Cone" wrote:


My guess is areas 23 and 24 are failing because they exceed 255
characters in length.
255 characters is a common limitation on the length of text.
Area 22 is 252 characters in length while 23 and 24 are 264 and 276.
As a test try whacking off 25 characters from the end of each and
see if it flies.
Also, you do not have to select a range to set a print area...
'--
'Studies = 22
ActiveSheet.PageSetup.PrintArea = _
Range("C36:U119,C236:U319,C436:U519,C636:U719,C836 :U919,C1036:U1119,C1236:U1319," & _
"C1436:U1519,C1636:U1719,C1836:U1919,C2036:U2119,C 2236:U2319,C2436:U2519," & _
"C2636:U2719,C2836:U2919,C3036:U3119,C3236:U3319,C 3436:U3519,C3636:U3719," & _
"C3836:U3919,C4036:U4119,C4236:U4319").Address

--
Jim Cone
Portland, Oregon USA



"Tom Joseph"

wrote in message
I am trying to get VBA to set a long print range. I have been able to get
this to work and set up printing for 22 reports. I would like it to be able
to work for 23-24 reports, but it appears I dont have the VBA
continuation/wrapping code right or it is a VBA limitataion.
I have commented out the 23 and 24 report options, after a number of
attempts at using some kind of continuation syntax.
Can someone please help with this? If this cannot be directly coded,
perhaps there is a workaround.
Thanks.
-snip-

JLGWhiz
Long print ranges in VBA
 
Hi Tom, I could not find the limit number in the Excel Specifications and
Limits, but by process of elimination it appears that there is a limit to the
number of discontiguous ranges that Excel will accept.

"Tom Joseph" wrote:

I am trying to get VBA to set a long print range. I have been able to get
this to work and set up printing for 22 reports. I would like it to be able
to work for 23-24 reports, but it appears I dont have the VBA
continuation/wrapping code right or it is a VBA limitataion.

I have commented out the 23 and 24 report options, after a number of
attempts at using some kind of continuation syntax.

Can someone please help with this? If this cannot be directly coded,
perhaps there is a workaround.

Thanks.


If n = 22 Then
'Studies = 22
Sheets("Report").Select
Range( _

"C36:U119,C236:U319,C436:U519,C636:U719,C836:U919, C1036:U1119,C1236:U1319,C1436:U1519,C1636:U1719,C1 836:U1919,C2036:U2119,C2236:U2319,C2436:U2519,C263 6:U2719,C2836:U2919,C3036:U3119,C3236:U3319,C3436: U3519,C3636:U3719,C3836:U3919,C4036:U4119,C4236:U4 319" _
).Select
ActiveSheet.PageSetup.PrintArea = _

"C36:U119,C236:U319,C436:U519,C636:U719,C836:U919, C1036:U1119,C1236:U1319,C1436:U1519,C1636:U1719,C1 836:U1919,C2036:U2119,C2236:U2319,C2436:U2519,C263 6:U2719,C2836:U2919,C3036:U3119,C3236:U3319,C3436: U3519,C3636:U3719,C3836:U3919,C4036:U4119,C4236:U4 319"
End If

'If n = 23 Then
'Studies = 23
' Sheets("Report").Select
' Range( _
'
"C36:U119,C236:U319,C436:U519,C636:U719,C836:U919, C1036:U1119,C1236:U1319,C1436:U1519,C1636:U1719,C1 836:U1919,C2036:U2119,C2236:U2319,C2436:U2519,C263 6:U2719,C2836:U2919,C3036:U3119,C3236:U3319,C3436: U3519,C3636:U3719,C3836:U3919,C4036:U4119,C4236:U4 319,C4436:U4519" _
' ).Select
' ActiveSheet.PageSetup.PrintArea = _
'
"C36:U119,C236:U319,C436:U519,C636:U719,C836:U919, C1036:U1119,C1236:U1319,C1436:U1519,C1636:U1719,C1 836:U1919,C2036:U2119,C2236:U2319,C2436:U2519,C263 6:U2719,C2836:U2919,C3036:U3119,C3236:U3319,C3436: U3519,C3636:U3719,C3836:U3919,C4036:U4119,C4236:U4 319,C4436:U4519"
'End If

'If n = 24 Then
'Studies = 24
' Sheets("Report").Select
' Range( _
'
"C36:U119,C236:U319,C436:U519,C636:U719,C836:U919, C1036:U1119,C1236:U1319,C1436:U1519,C1636:U1719,C1 836:U1919,C2036:U2119,C2236:U2319,C2436:U2519,C263 6:U2719,C2836:U2919,C3036:U3119,C3236:U3319,C3436: U3519,C3636:U3719,C3836:U3919,C4036:U4119,C4236:U4 319,C4436:U4519,C4636:U4719" _
' ).Select
' ActiveSheet.PageSetup.PrintArea = _
'
"C36:U119,C236:U319,C436:U519,C636:U719,C836:U919, C1036:U1119,C1236:U1319,C1436:U1519,C1636:U1719,C1 836:U1919,C2036:U2119,C2236:U2319,C2436:U2519,C263 6:U2719,C2836:U2919,C3036:U3119,C3236:U3319,C3436: U3519,C3636:U3719,C3836:U3919,C4036:U4119,C4236:U4 319,C4436:U4519,C4636:U4719"
'End If

Dave Peterson
Long print ranges in VBA
 
Each of these ranges will print on a separate piece of paper, right?

And if strings are too long, maybe you could just use a range object.

(Untested)

Dim myRng as range
with worksheets("Report")
set myrng = union(.range("C36:U119,C236:U319,C436:U519,C636:U7 19,C836:U919"), _
.range("C1236:U1319,C1436:U1519,C1636:U1719,C1836: U1919"), _
.range("..I got bored.."))
.PageSetup.PrintArea = myrng.address(external:=true)
end with

======
I didn't look at all your ranges, but it looks as though (some) are nicely
spaced out:

Dim myRng as range
dim iCtr as long
with worksheets("Report")
for ictr = 0 to 4200 step 200
if myrng is nothing then
set myrng = .cells(ictr+36,"C").resize(84,19)
else
set myrng = union(myrng, .cells(ictr+36,"C").resize(84,19))
end if
next ictr
.PageSetup.PrintArea = myrng.address(external:=true)
end with


Tom Joseph wrote:

I am trying to get VBA to set a long print range. I have been able to get
this to work and set up printing for 22 reports. I would like it to be able
to work for 23-24 reports, but it appears I dont have the VBA
continuation/wrapping code right or it is a VBA limitataion.

I have commented out the 23 and 24 report options, after a number of
attempts at using some kind of continuation syntax.

Can someone please help with this? If this cannot be directly coded,
perhaps there is a workaround.

Thanks.

If n = 22 Then
'Studies = 22
Sheets("Report").Select
Range( _

"C36:U119,C236:U319,C436:U519,C636:U719,C836:U919, C1036:U1119,C1236:U1319,C1436:U1519,C1636:U1719,C1 836:U1919,C2036:U2119,C2236:U2319,C2436:U2519,C263 6:U2719,C2836:U2919,C3036:U3119,C3236:U3319,C3436: U3519,C3636:U3719,C3836:U3919,C4036:U4119,C4236:U4 319" _
).Select
ActiveSheet.PageSetup.PrintArea = _

"C36:U119,C236:U319,C436:U519,C636:U719,C836:U919, C1036:U1119,C1236:U1319,C1436:U1519,C1636:U1719,C1 836:U1919,C2036:U2119,C2236:U2319,C2436:U2519,C263 6:U2719,C2836:U2919,C3036:U3119,C3236:U3319,C3436: U3519,C3636:U3719,C3836:U3919,C4036:U4119,C4236:U4 319"
End If

'If n = 23 Then
'Studies = 23
' Sheets("Report").Select
' Range( _
'
"C36:U119,C236:U319,C436:U519,C636:U719,C836:U919, C1036:U1119,C1236:U1319,C1436:U1519,C1636:U1719,C1 836:U1919,C2036:U2119,C2236:U2319,C2436:U2519,C263 6:U2719,C2836:U2919,C3036:U3119,C3236:U3319,C3436: U3519,C3636:U3719,C3836:U3919,C4036:U4119,C4236:U4 319,C4436:U4519" _
' ).Select
' ActiveSheet.PageSetup.PrintArea = _
'
"C36:U119,C236:U319,C436:U519,C636:U719,C836:U919, C1036:U1119,C1236:U1319,C1436:U1519,C1636:U1719,C1 836:U1919,C2036:U2119,C2236:U2319,C2436:U2519,C263 6:U2719,C2836:U2919,C3036:U3119,C3236:U3319,C3436: U3519,C3636:U3719,C3836:U3919,C4036:U4119,C4236:U4 319,C4436:U4519"
'End If

'If n = 24 Then
'Studies = 24
' Sheets("Report").Select
' Range( _
'
"C36:U119,C236:U319,C436:U519,C636:U719,C836:U919, C1036:U1119,C1236:U1319,C1436:U1519,C1636:U1719,C1 836:U1919,C2036:U2119,C2236:U2319,C2436:U2519,C263 6:U2719,C2836:U2919,C3036:U3119,C3236:U3319,C3436: U3519,C3636:U3719,C3836:U3919,C4036:U4119,C4236:U4 319,C4436:U4519,C4636:U4719" _
' ).Select
' ActiveSheet.PageSetup.PrintArea = _
'
"C36:U119,C236:U319,C436:U519,C636:U719,C836:U919, C1036:U1119,C1236:U1319,C1436:U1519,C1636:U1719,C1 836:U1919,C2036:U2119,C2236:U2319,C2436:U2519,C263 6:U2719,C2836:U2919,C3036:U3119,C3236:U3319,C3436: U3519,C3636:U3719,C3836:U3919,C4036:U4119,C4236:U4 319,C4436:U4519,C4636:U4719"
'End If

--

Dave Peterson

Jim Cone[_2_]
Long print ranges in VBA
 

I included an example is at the bottom of my first post.
However, my example could be modified slightly to include the
sheet name instead of using ActiveSheet ...

'--
Sheets("Report").PageSetup.PrintArea = _
Range("C36:U119,C236:U319,C436:U519,C636:U719,C836 :U919,C1036:U1119,C1236:U1319," & _
"C1436:U1519,C1636:U1719,C1836:U1919,C2036:U2119,C 2236:U2319,C2436:U2519," & _
"C2636:U2719,C2836:U2919,C3036:U3119,C3236:U3319,C 3436:U3519,C3636:U3719," & _
"C3836:U3919,C4036:U4119,C4236:U4319").Address
'--
The above references the "Report" sheet without selecting anything.
--
Jim Cone
Portland, Oregon USA


"Tom Joseph"

wrote in message
Thanks for the note, Jim.
Can you elaborate on seting a print area without selecting a range (in VBA)?
Perhaps a brief example will help me get started.
These are 24 noncontigous ranges.
Thanks,
Tom

Tom Joseph
Long print ranges in VBA
 
Thanks, Jim.

I missed the example earier. I will give it a try.

"Jim Cone" wrote:


I included an example is at the bottom of my first post.
However, my example could be modified slightly to include the
sheet name instead of using ActiveSheet ...

'--
Sheets("Report").PageSetup.PrintArea = _
Range("C36:U119,C236:U319,C436:U519,C636:U719,C836 :U919,C1036:U1119,C1236:U1319," & _
"C1436:U1519,C1636:U1719,C1836:U1919,C2036:U2119,C 2236:U2319,C2436:U2519," & _
"C2636:U2719,C2836:U2919,C3036:U3119,C3236:U3319,C 3436:U3519,C3636:U3719," & _
"C3836:U3919,C4036:U4119,C4236:U4319").Address
'--
The above references the "Report" sheet without selecting anything.
--
Jim Cone
Portland, Oregon USA


"Tom Joseph"

wrote in message
Thanks for the note, Jim.
Can you elaborate on seting a print area without selecting a range (in VBA)?
Perhaps a brief example will help me get started.
These are 24 noncontigous ranges.
Thanks,
Tom

Tom Joseph
Long print ranges in VBA
 
Hi Dave,

I very much appreciate the suggestion and example. I will give it a try.

Best,

Tom

"Dave Peterson" wrote:

Each of these ranges will print on a separate piece of paper, right?

And if strings are too long, maybe you could just use a range object.

(Untested)

Dim myRng as range
with worksheets("Report")
set myrng = union(.range("C36:U119,C236:U319,C436:U519,C636:U7 19,C836:U919"), _
.range("C1236:U1319,C1436:U1519,C1636:U1719,C1836: U1919"), _
.range("..I got bored.."))
.PageSetup.PrintArea = myrng.address(external:=true)
end with

======
I didn't look at all your ranges, but it looks as though (some) are nicely
spaced out:

Dim myRng as range
dim iCtr as long
with worksheets("Report")
for ictr = 0 to 4200 step 200
if myrng is nothing then
set myrng = .cells(ictr+36,"C").resize(84,19)
else
set myrng = union(myrng, .cells(ictr+36,"C").resize(84,19))
end if
next ictr
.PageSetup.PrintArea = myrng.address(external:=true)
end with


Tom Joseph wrote:

I am trying to get VBA to set a long print range. I have been able to get
this to work and set up printing for 22 reports. I would like it to be able
to work for 23-24 reports, but it appears I dont have the VBA
continuation/wrapping code right or it is a VBA limitataion.

I have commented out the 23 and 24 report options, after a number of
attempts at using some kind of continuation syntax.

Can someone please help with this? If this cannot be directly coded,
perhaps there is a workaround.

Thanks.

If n = 22 Then
'Studies = 22
Sheets("Report").Select
Range( _

"C36:U119,C236:U319,C436:U519,C636:U719,C836:U919, C1036:U1119,C1236:U1319,C1436:U1519,C1636:U1719,C1 836:U1919,C2036:U2119,C2236:U2319,C2436:U2519,C263 6:U2719,C2836:U2919,C3036:U3119,C3236:U3319,C3436: U3519,C3636:U3719,C3836:U3919,C4036:U4119,C4236:U4 319" _
).Select
ActiveSheet.PageSetup.PrintArea = _

"C36:U119,C236:U319,C436:U519,C636:U719,C836:U919, C1036:U1119,C1236:U1319,C1436:U1519,C1636:U1719,C1 836:U1919,C2036:U2119,C2236:U2319,C2436:U2519,C263 6:U2719,C2836:U2919,C3036:U3119,C3236:U3319,C3436: U3519,C3636:U3719,C3836:U3919,C4036:U4119,C4236:U4 319"
End If

'If n = 23 Then
'Studies = 23
' Sheets("Report").Select
' Range( _
'
"C36:U119,C236:U319,C436:U519,C636:U719,C836:U919, C1036:U1119,C1236:U1319,C1436:U1519,C1636:U1719,C1 836:U1919,C2036:U2119,C2236:U2319,C2436:U2519,C263 6:U2719,C2836:U2919,C3036:U3119,C3236:U3319,C3436: U3519,C3636:U3719,C3836:U3919,C4036:U4119,C4236:U4 319,C4436:U4519" _
' ).Select
' ActiveSheet.PageSetup.PrintArea = _
'
"C36:U119,C236:U319,C436:U519,C636:U719,C836:U919, C1036:U1119,C1236:U1319,C1436:U1519,C1636:U1719,C1 836:U1919,C2036:U2119,C2236:U2319,C2436:U2519,C263 6:U2719,C2836:U2919,C3036:U3119,C3236:U3319,C3436: U3519,C3636:U3719,C3836:U3919,C4036:U4119,C4236:U4 319,C4436:U4519"
'End If

'If n = 24 Then
'Studies = 24
' Sheets("Report").Select
' Range( _
'
"C36:U119,C236:U319,C436:U519,C636:U719,C836:U919, C1036:U1119,C1236:U1319,C1436:U1519,C1636:U1719,C1 836:U1919,C2036:U2119,C2236:U2319,C2436:U2519,C263 6:U2719,C2836:U2919,C3036:U3119,C3236:U3319,C3436: U3519,C3636:U3719,C3836:U3919,C4036:U4119,C4236:U4 319,C4436:U4519,C4636:U4719" _
' ).Select
' ActiveSheet.PageSetup.PrintArea = _
'
"C36:U119,C236:U319,C436:U519,C636:U719,C836:U919, C1036:U1119,C1236:U1319,C1436:U1519,C1636:U1719,C1 836:U1919,C2036:U2119,C2236:U2319,C2436:U2519,C263 6:U2719,C2836:U2919,C3036:U3119,C3236:U3319,C3436: U3519,C3636:U3719,C3836:U3919,C4036:U4119,C4236:U4 319,C4436:U4519,C4636:U4719"
'End If

--

Dave Peterson

Tom Joseph
Long print ranges in VBA
 
Hi Jim,

Based on your input, I tried the following. It has ranges for 24 pages, but
only sets up printing for 16 pages. I guess there must be some limitation to
this function. Any ideas?

I am trying to set 24 separate print ranges taht can be used to create a
single 24 page PDF. 24 separate PDFs would not be workable.




Sheets("Report").PageSetup.PrintArea = _
Range("C36:U119,C236:U319,C436:U519,C636:U719,C836 :U919,C1036:U1119,C1236:U1319," & _
"C1436:U1519,C1636:U1719,C1836:U1919,C2036:U2119,C 2236:U2319,C2436:U2519," & _
"C2636:U2719,C2836:U2919,C3036:U3119,C3236:U3319,C 3436:U3519,C3636:U3719," & _
"C3836:U3919,C4036:U4119,C4236:U4319").Address





"Jim Cone" wrote:


I included an example is at the bottom of my first post.
However, my example could be modified slightly to include the
sheet name instead of using ActiveSheet ...

'--
Sheets("Report").PageSetup.PrintArea = _
Range("C36:U119,C236:U319,C436:U519,C636:U719,C836 :U919,C1036:U1119,C1236:U1319," & _
"C1436:U1519,C1636:U1719,C1836:U1919,C2036:U2119,C 2236:U2319,C2436:U2519," & _
"C2636:U2719,C2836:U2919,C3036:U3119,C3236:U3319,C 3436:U3519,C3636:U3719," & _
"C3836:U3919,C4036:U4119,C4236:U4319").Address
'--
The above references the "Report" sheet without selecting anything.
--
Jim Cone
Portland, Oregon USA


"Tom Joseph"

wrote in message
Thanks for the note, Jim.
Can you elaborate on seting a print area without selecting a range (in VBA)?
Perhaps a brief example will help me get started.
These are 24 noncontigous ranges.
Thanks,
Tom

Tom Joseph
Long print ranges in VBA
 
Hi Dave,

Based on your input, I tried the following. It has ranges for 24 pages, but
only sets up printing for 14 pages. I guess there must be some limitation to
this function. Any ideas?

I am trying to set 24 separate print ranges that can be used to create a
single 24 page PDF. 24 separate PDFs would not be workable.

Dim myRng As Range
With Worksheets("Report")
Set myRng =
Union(.Range("C36:U119,C236:U319,C436:U519,C636:U7 19,C836:U919"), _

..Range("C1036:U1119,C1236:U1319,C1436:U1519,C1636 :U1719"), _

..Range("C1836:U1919,C2036:U2119,C2236:U2319,C2436 :U2519"), _

..Range("C2636:U2719,C2836:U2919,C3036:U3119,C3236 :U3319"), _

..Range("C3436:U3519,C3636:U3719,C3836:U3919,C4036 :U4119"), _
.Range("C4236:U4319,C4436:U4519,C4636:U4719"))
.PageSetup.PrintArea = myRng.Address(external:=True)
End With


"Dave Peterson" wrote:

Each of these ranges will print on a separate piece of paper, right?

And if strings are too long, maybe you could just use a range object.

(Untested)

Dim myRng as range
with worksheets("Report")
set myrng = union(.range("C36:U119,C236:U319,C436:U519,C636:U7 19,C836:U919"), _
.range("C1236:U1319,C1436:U1519,C1636:U1719,C1836: U1919"), _
.range("..I got bored.."))
.PageSetup.PrintArea = myrng.address(external:=true)
end with

======
I didn't look at all your ranges, but it looks as though (some) are nicely
spaced out:

Dim myRng as range
dim iCtr as long
with worksheets("Report")
for ictr = 0 to 4200 step 200
if myrng is nothing then
set myrng = .cells(ictr+36,"C").resize(84,19)
else
set myrng = union(myrng, .cells(ictr+36,"C").resize(84,19))
end if
next ictr
.PageSetup.PrintArea = myrng.address(external:=true)
end with


Tom Joseph wrote:

I am trying to get VBA to set a long print range. I have been able to get
this to work and set up printing for 22 reports. I would like it to be able
to work for 23-24 reports, but it appears I dont have the VBA
continuation/wrapping code right or it is a VBA limitataion.

I have commented out the 23 and 24 report options, after a number of
attempts at using some kind of continuation syntax.

Can someone please help with this? If this cannot be directly coded,
perhaps there is a workaround.

Thanks.

If n = 22 Then
'Studies = 22
Sheets("Report").Select
Range( _

"C36:U119,C236:U319,C436:U519,C636:U719,C836:U919, C1036:U1119,C1236:U1319,C1436:U1519,C1636:U1719,C1 836:U1919,C2036:U2119,C2236:U2319,C2436:U2519,C263 6:U2719,C2836:U2919,C3036:U3119,C3236:U3319,C3436: U3519,C3636:U3719,C3836:U3919,C4036:U4119,C4236:U4 319" _
).Select
ActiveSheet.PageSetup.PrintArea = _

"C36:U119,C236:U319,C436:U519,C636:U719,C836:U919, C1036:U1119,C1236:U1319,C1436:U1519,C1636:U1719,C1 836:U1919,C2036:U2119,C2236:U2319,C2436:U2519,C263 6:U2719,C2836:U2919,C3036:U3119,C3236:U3319,C3436: U3519,C3636:U3719,C3836:U3919,C4036:U4119,C4236:U4 319"
End If

'If n = 23 Then
'Studies = 23
' Sheets("Report").Select
' Range( _
'
"C36:U119,C236:U319,C436:U519,C636:U719,C836:U919, C1036:U1119,C1236:U1319,C1436:U1519,C1636:U1719,C1 836:U1919,C2036:U2119,C2236:U2319,C2436:U2519,C263 6:U2719,C2836:U2919,C3036:U3119,C3236:U3319,C3436: U3519,C3636:U3719,C3836:U3919,C4036:U4119,C4236:U4 319,C4436:U4519" _
' ).Select
' ActiveSheet.PageSetup.PrintArea = _
'
"C36:U119,C236:U319,C436:U519,C636:U719,C836:U919, C1036:U1119,C1236:U1319,C1436:U1519,C1636:U1719,C1 836:U1919,C2036:U2119,C2236:U2319,C2436:U2519,C263 6:U2719,C2836:U2919,C3036:U3119,C3236:U3319,C3436: U3519,C3636:U3719,C3836:U3919,C4036:U4119,C4236:U4 319,C4436:U4519"
'End If

'If n = 24 Then
'Studies = 24
' Sheets("Report").Select
' Range( _
'
"C36:U119,C236:U319,C436:U519,C636:U719,C836:U919, C1036:U1119,C1236:U1319,C1436:U1519,C1636:U1719,C1 836:U1919,C2036:U2119,C2236:U2319,C2436:U2519,C263 6:U2719,C2836:U2919,C3036:U3119,C3236:U3319,C3436: U3519,C3636:U3719,C3836:U3919,C4036:U4119,C4236:U4 319,C4436:U4519,C4636:U4719" _
' ).Select
' ActiveSheet.PageSetup.PrintArea = _
'
"C36:U119,C236:U319,C436:U519,C636:U719,C836:U919, C1036:U1119,C1236:U1319,C1436:U1519,C1636:U1719,C1 836:U1919,C2036:U2119,C2236:U2319,C2436:U2519,C263 6:U2719,C2836:U2919,C3036:U3119,C3236:U3319,C3436: U3519,C3636:U3719,C3836:U3919,C4036:U4119,C4236:U4 319,C4436:U4519,C4636:U4719"
'End If

--

Dave Peterson

Jim Cone[_2_]
Long print ranges in VBA
 
Tom,
You print area string is cut off at 180 characters.
Looks like the limitation is ~180 characters.
I set up 25 print areas, but they number of characters in the string
was under 180.
It appears Excel will accept strings up to 255 characters but will
only create print areas for the first ~180 characters in the string.
My guess is that Excel is doing something to the string that adds
characters necessary for processing.
I have no solution for you other than to consolidate some of the areas.

For what it's worth, my "Special Print" Excel add-in has a utility that creates
multiple print areas from all of the selections on a sheet.
However, it can't overcome the built-in limitation.
--
Jim Cone
Portland, Oregon USA




"Tom Joseph"

wrote in message
Hi Jim,
Based on your input, I tried the following. It has ranges for 24 pages, but
only sets up printing for 16 pages. I guess there must be some limitation to
this function. Any ideas?
I am trying to set 24 separate print ranges taht can be used to create a
single 24 page PDF. 24 separate PDFs would not be workable.




Sheets("Report").PageSetup.PrintArea = _
Range("C36:U119,C236:U319,C436:U519,C636:U719,C836 :U919,C1036:U1119,C1236:U1319," & _
"C1436:U1519,C1636:U1719,C1836:U1919,C2036:U2119,C 2236:U2319,C2436:U2519," & _
"C2636:U2719,C2836:U2919,C3036:U3119,C3236:U3319,C 3436:U3519,C3636:U3719," & _
"C3836:U3919,C4036:U4119,C4236:U4319").Address





"Jim Cone" wrote:


I included an example is at the bottom of my first post.
However, my example could be modified slightly to include the
sheet name instead of using ActiveSheet ...

'--
Sheets("Report").PageSetup.PrintArea = _
Range("C36:U119,C236:U319,C436:U519,C636:U719,C836 :U919,C1036:U1119,C1236:U1319," & _
"C1436:U1519,C1636:U1719,C1836:U1919,C2036:U2119,C 2236:U2319,C2436:U2519," & _
"C2636:U2719,C2836:U2919,C3036:U3119,C3236:U3319,C 3436:U3519,C3636:U3719," & _
"C3836:U3919,C4036:U4119,C4236:U4319").Address
'--
The above references the "Report" sheet without selecting anything.
--
Jim Cone
Portland, Oregon USA


"Tom Joseph"

wrote in message
Thanks for the note, Jim.
Can you elaborate on seting a print area without selecting a range (in VBA)?
Perhaps a brief example will help me get started.
These are 24 noncontigous ranges.
Thanks,
Tom

Jim Cone[_2_]
Long print ranges in VBA
 
Aha !,
Change the end of the code...

From:
.Address
To:
..Address(0, 0)

That removes the absolute reference characters "$" before Excel has to.
It should get you almost there.
--
Jim Cone
Portland, Oregon USA

Tom Joseph
Long print ranges in VBA
 
Thanks, Jim. I will give it a try.

Best,

Tom

"Jim Cone" wrote:

Aha !,
Change the end of the code...

From:
.Address
To:
..Address(0, 0)

That removes the absolute reference characters "$" before Excel has to.
It should get you almost there.
--
Jim Cone
Portland, Oregon USA

Dave Peterson
Long print ranges in VBA
 
I think that Jim explained the problem--it's the length of the string.

And I don't know enough about whatever PDF writer you're using to know how it'll
print.

A couple of alternatives...

#1.
Remove all the manual page breaks
Hide the rows/columns that you don't want to print.
Add a manual page break where you want them (before the start of each
section???)
Print the worksheet.

#2.
Copy the ranges to a different worksheet (as values or as a picture).
Add page breaks where you want them
Print that helper worksheet.

Tom Joseph wrote:

Hi Dave,

Based on your input, I tried the following. It has ranges for 24 pages, but
only sets up printing for 14 pages. I guess there must be some limitation to
this function. Any ideas?

I am trying to set 24 separate print ranges that can be used to create a
single 24 page PDF. 24 separate PDFs would not be workable.

Dim myRng As Range
With Worksheets("Report")
Set myRng =
Union(.Range("C36:U119,C236:U319,C436:U519,C636:U7 19,C836:U919"), _

.Range("C1036:U1119,C1236:U1319,C1436:U1519,C1636: U1719"), _

.Range("C1836:U1919,C2036:U2119,C2236:U2319,C2436: U2519"), _

.Range("C2636:U2719,C2836:U2919,C3036:U3119,C3236: U3319"), _

.Range("C3436:U3519,C3636:U3719,C3836:U3919,C4036: U4119"), _
.Range("C4236:U4319,C4436:U4519,C4636:U4719"))
.PageSetup.PrintArea = myRng.Address(external:=True)
End With

"Dave Peterson" wrote:

Each of these ranges will print on a separate piece of paper, right?

And if strings are too long, maybe you could just use a range object.

(Untested)

Dim myRng as range
with worksheets("Report")
set myrng = union(.range("C36:U119,C236:U319,C436:U519,C636:U7 19,C836:U919"), _
.range("C1236:U1319,C1436:U1519,C1636:U1719,C1836: U1919"), _
.range("..I got bored.."))
.PageSetup.PrintArea = myrng.address(external:=true)
end with

======
I didn't look at all your ranges, but it looks as though (some) are nicely
spaced out:

Dim myRng as range
dim iCtr as long
with worksheets("Report")
for ictr = 0 to 4200 step 200
if myrng is nothing then
set myrng = .cells(ictr+36,"C").resize(84,19)
else
set myrng = union(myrng, .cells(ictr+36,"C").resize(84,19))
end if
next ictr
.PageSetup.PrintArea = myrng.address(external:=true)
end with


Tom Joseph wrote:

I am trying to get VBA to set a long print range. I have been able to get
this to work and set up printing for 22 reports. I would like it to be able
to work for 23-24 reports, but it appears I dont have the VBA
continuation/wrapping code right or it is a VBA limitataion.

I have commented out the 23 and 24 report options, after a number of
attempts at using some kind of continuation syntax.

Can someone please help with this? If this cannot be directly coded,
perhaps there is a workaround.

Thanks.

If n = 22 Then
'Studies = 22
Sheets("Report").Select
Range( _

"C36:U119,C236:U319,C436:U519,C636:U719,C836:U919, C1036:U1119,C1236:U1319,C1436:U1519,C1636:U1719,C1 836:U1919,C2036:U2119,C2236:U2319,C2436:U2519,C263 6:U2719,C2836:U2919,C3036:U3119,C3236:U3319,C3436: U3519,C3636:U3719,C3836:U3919,C4036:U4119,C4236:U4 319" _
).Select
ActiveSheet.PageSetup.PrintArea = _

"C36:U119,C236:U319,C436:U519,C636:U719,C836:U919, C1036:U1119,C1236:U1319,C1436:U1519,C1636:U1719,C1 836:U1919,C2036:U2119,C2236:U2319,C2436:U2519,C263 6:U2719,C2836:U2919,C3036:U3119,C3236:U3319,C3436: U3519,C3636:U3719,C3836:U3919,C4036:U4119,C4236:U4 319"
End If

'If n = 23 Then
'Studies = 23
' Sheets("Report").Select
' Range( _
'
"C36:U119,C236:U319,C436:U519,C636:U719,C836:U919, C1036:U1119,C1236:U1319,C1436:U1519,C1636:U1719,C1 836:U1919,C2036:U2119,C2236:U2319,C2436:U2519,C263 6:U2719,C2836:U2919,C3036:U3119,C3236:U3319,C3436: U3519,C3636:U3719,C3836:U3919,C4036:U4119,C4236:U4 319,C4436:U4519" _
' ).Select
' ActiveSheet.PageSetup.PrintArea = _
'
"C36:U119,C236:U319,C436:U519,C636:U719,C836:U919, C1036:U1119,C1236:U1319,C1436:U1519,C1636:U1719,C1 836:U1919,C2036:U2119,C2236:U2319,C2436:U2519,C263 6:U2719,C2836:U2919,C3036:U3119,C3236:U3319,C3436: U3519,C3636:U3719,C3836:U3919,C4036:U4119,C4236:U4 319,C4436:U4519"
'End If

'If n = 24 Then
'Studies = 24
' Sheets("Report").Select
' Range( _
'
"C36:U119,C236:U319,C436:U519,C636:U719,C836:U919, C1036:U1119,C1236:U1319,C1436:U1519,C1636:U1719,C1 836:U1919,C2036:U2119,C2236:U2319,C2436:U2519,C263 6:U2719,C2836:U2919,C3036:U3119,C3236:U3319,C3436: U3519,C3636:U3719,C3836:U3919,C4036:U4119,C4236:U4 319,C4436:U4519,C4636:U4719" _
' ).Select
' ActiveSheet.PageSetup.PrintArea = _
'
"C36:U119,C236:U319,C436:U519,C636:U719,C836:U919, C1036:U1119,C1236:U1319,C1436:U1519,C1636:U1719,C1 836:U1919,C2036:U2119,C2236:U2319,C2436:U2519,C263 6:U2719,C2836:U2919,C3036:U3119,C3236:U3319,C3436: U3519,C3636:U3719,C3836:U3919,C4036:U4119,C4236:U4 319,C4436:U4519,C4636:U4719"
'End If

--

Dave Peterson


--

Dave Peterson


All times are GMT +1. The time now is 12:31 AM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com