Print Area:when it comes to printing,

Hi

I have data constantly changing from using 100 to 1500 lines and when it
comes to printing, I have to constantly set the print area or drag the print
preview handles. Is there a way that a piece of code can find the last
occupied cell in a column and have the page set up change accordingly?

Many thanks in advance

John

John,
With ActiveSheet
.PageSetup.PrintArea = .UsedRange.Address
End With

NickHK

"JohnUK" <JohnUK@discussions.microsoft.com> wrote in message
news:64553F53-5570-42A6-A503-39855B6B95DA@microsoft.com...
> Hi
>
> I have data constantly changing from using 100 to 1500 lines and when it
> comes to printing, I have to constantly set the print area or drag the
print
> preview handles. Is there a way that a piece of code can find the last
> occupied cell in a column and have the page set up change accordingly?
>
> Many thanks in advance
>
> John

Hi Nick, thanks for your help.
I have tried it, but it doesnt work, maybe because I have formulas that run
down each side of the data that I want printed!!
John

"NickHK" wrote:

> John,
> With ActiveSheet
> .PageSetup.PrintArea = .UsedRange.Address
> End With
>
> NickHK
>
> "JohnUK" <JohnUK@discussions.microsoft.com> wrote in message
> news:64553F53-5570-42A6-A503-39855B6B95DA@microsoft.com...
> > Hi
> >
> > I have data constantly changing from using 100 to 1500 lines and when it
> > comes to printing, I have to constantly set the print area or drag the
> print
> > preview handles. Is there a way that a piece of code can find the last
> > occupied cell in a column and have the page set up change accordingly?
> >
> > Many thanks in advance
> >
> > John
>
>
>

Hi John,

With ActiveSheet
.PageSetup.PrintArea =
intersect(.UsedRange.Address,range("a:b")).address
End With

Change "a:b" to be columns you want printed.

Regards,
Ivan

Hi Ivan - again

It comes back as a run time error - object required

Any ideas ?

John

"Ivan Raiminius" wrote:

> Hi John,
>
> With ActiveSheet
> .PageSetup.PrintArea =
> intersect(.UsedRange.Address,range("a:b")).address
> End With
>
> Change "a:b" to be columns you want printed.
>
> Regards,
> Ivan
>
>

Hi John,

sorry, should be:
intersect(.UsedRange,range("a:b")).address

Regards,
Ivan

John,
I'm sure Ivan will see it, but remove the .address from .usedrange.

NickHK

"JohnUK" <JohnUK@discussions.microsoft.com> wrote in message
news:5740B382-FDAF-4C77-A02E-2E53BD7839CB@microsoft.com...
> Hi Ivan - again
>
> It comes back as a run time error - object required
>
> Any ideas ?
>
> John
>
> "Ivan Raiminius" wrote:
>
> > Hi John,
> >
> > With ActiveSheet
> > .PageSetup.PrintArea =
> > intersect(.UsedRange.Address,range("a:b")).address
> > End With
> >
> > Change "a:b" to be columns you want printed.
> >
> > Regards,
> > Ivan
> >
> >

Hi Nick,

thanks, you're right. And I already corrected myself (probably the post
was not visible to you as it takes some time to display)

Regards,
Ivan

Hi Ivan,

That worked, but I still have a problem and I think it has something to do
with my range's

The area I want to print is a pivot table along with some formulas that run
alongside, and because I need the formulas to refresh, I put them into a
range that goes up to 1500 lines. As an experiment I shrunk the range down to
500 lines, ran your code and walla the print area ended on the 500th line.

John

"Ivan Raiminius" wrote:

> Hi John,
>
> sorry, should be:
> intersect(.UsedRange,range("a:b")).address
>
> Regards,
> Ivan
>
>

Hi John,

try to reset last used cell with this:

Dim x As Long
x = ActiveWorksheet.UsedRange.Rows.Count

'and continue with setting printarea
With ActiveSheet
.PageSetup.PrintArea = intersect(.UsedRange,range("a:b")).address
End With

Regards,
Ivan

Sorry Ivan - being a pain again.

I tried it and got the run time again with Object Required

John

"Ivan Raiminius" wrote:

> Hi John,
>
> try to reset last used cell with this:
>
> Dim x As Long
> x = ActiveWorksheet.UsedRange.Rows.Count
>
> 'and continue with setting printarea
> With ActiveSheet
> .PageSetup.PrintArea = intersect(.UsedRange,range("a:b")).address
> End With
>
> Regards,
> Ivan
>
>

Hi John,

what line?

Regards,
Ivan

Stops on this:

x = ActiveWorksheet.UsedRange.Rows.count

John

"Ivan Raiminius" wrote:

> Hi John,
>
> what line?
>
> Regards,
> Ivan
>
>

Hi John,

rearange the code like this:

Dim x As Long
With ActiveSheet
x = .UsedRange.Rows.Count
.PageSetup.PrintArea = intersect(.UsedRange,range("a:b")).address
End With

Regards,
Ivan

Hi Ivan, thanks for your patience, but I think your going to give up on me,
because now I am back to where I was before. I entered your latest code but
the page setup still goes down to the end of the range and bypasses all the
data.
John

"Ivan Raiminius" wrote:

> Hi John,
>
> rearange the code like this:
>
> Dim x As Long
> With ActiveSheet
> x = .UsedRange.Rows.Count
> .PageSetup.PrintArea = intersect(.UsedRange,range("a:b")).address
> End With
>
> Regards,
> Ivan
>
>

Hi John,

let's try different attitude:

dim i as long
dim j as long
dim rng as range
set rng=range("b1..e1") ' the address of first row of data you want to
print out
j=0
for i = 1 to rng.columns.count
j=worksheetfunction.max(j,rng.cells(application.Rows.Count-rng.Row,1).end(xlup).row)
next i
activesheet.pagesetup.printarea =
rng.Resize(j-rng.row+1,rng.Columns.Count).Address

Change "b1..e1" in "set rng=range("b1..e1")" to be address of first row
of data you want to print.

Please let me know if it worked.

Regards,
Ivan

Fantastic - Ivan you are a star.

Many thanks - much appreciated - and thanks to Nick

Take care

Regards

John

"Ivan Raiminius" wrote:

> Hi John,
>
> let's try different attitude:
>
> dim i as long
> dim j as long
> dim rng as range
> set rng=range("b1..e1") ' the address of first row of data you want to
> print out
> j=0
> for i = 1 to rng.columns.count
> j=worksheetfunction.max(j,rng.cells(application.Rows.Count-rng.Row,1).end(xlup).row)
> next i
> activesheet.pagesetup.printarea =
> rng.Resize(j-rng.row+1,rng.Columns.Count).Address
>
> Change "b1..e1" in "set rng=range("b1..e1")" to be address of first row
> of data you want to print.
>
> Please let me know if it worked.
>
> Regards,
> Ivan
>
>

Using the example posted:

Assume your pivotTable is in B1:D200 and you have formulas pre-entered in
E1:E1500

this code only prints out B1:D200? Or are you not printing out the
pre-entered formulas? (Pivot Table actually in B1:E200 and formulas in
F1:F1500 as an example - then rng = Range("B1:E1"))

Just curious - because I don't see how this solves the problem you described
if you want to include the pre-entered formulas.

--
Regards,
Tom Ogilvy


"JohnUK" wrote:

> Fantastic - Ivan you are a star.
>
> Many thanks - much appreciated - and thanks to Nick
>
> Take care
>
> Regards
>
> John
>
> "Ivan Raiminius" wrote:
>
> > Hi John,
> >
> > let's try different attitude:
> >
> > dim i as long
> > dim j as long
> > dim rng as range
> > set rng=range("b1..e1") ' the address of first row of data you want to
> > print out
> > j=0
> > for i = 1 to rng.columns.count
> > j=worksheetfunction.max(j,rng.cells(application.Rows.Count-rng.Row,1).end(xlup).row)
> > next i
> > activesheet.pagesetup.printarea =
> > rng.Resize(j-rng.row+1,rng.Columns.Count).Address
> >
> > Change "b1..e1" in "set rng=range("b1..e1")" to be address of first row
> > of data you want to print.
> >
> > Please let me know if it worked.
> >
> > Regards,
> > Ivan
> >
> >

Hi Tom,
Thanks for your input.
I wanted to print out some of the columns that contained formulas as well as
the pivot table and I think the line that contains
(application.Rows.Count-rng.Row,1) looks at the data in the first column
(ideal because of no formulas) and gives me what I am looking for.
However, I have tried to apply the same principle on a different page, but
this time not so easy and I think it’s because all the columns have formulas.
This is my poor way of trying to work around the problem:
I am trying to use code to enter a value into a different column so that
Toms code can do the same trick:

Range("E40").Select
If ActiveCell > 0 Then
Range("N40").Select
ActiveCell.FormulaR1C1 = "1"

Range("E41").Select
If ActiveCell > 0 Then
Range("N41").Select
ActiveCell.FormulaR1C1 = "1"

Range("E42").Select
If ActiveCell > 0 Then
Range("N42").Select
ActiveCell.FormulaR1C1 = "1"

And so on and so on (I need 50 lines done this way)
I know you must be laughing at me right now, but can you see what I am
trying to do?
May I ask for your help?
Either there is another way altogether or can the above code be shortened
somewhat?

Regards

John


"Tom Ogilvy" wrote:

> Using the example posted:
>
> Assume your pivotTable is in B1:D200 and you have formulas pre-entered in
> E1:E1500
>
> this code only prints out B1:D200? Or are you not printing out the
> pre-entered formulas? (Pivot Table actually in B1:E200 and formulas in
> F1:F1500 as an example - then rng = Range("B1:E1"))
>
> Just curious - because I don't see how this solves the problem you described
> if you want to include the pre-entered formulas.
>
> --
> Regards,
> Tom Ogilvy
>
>
> "JohnUK" wrote:
>
> > Fantastic - Ivan you are a star.
> >
> > Many thanks - much appreciated - and thanks to Nick
> >
> > Take care
> >
> > Regards
> >
> > John
> >
> > "Ivan Raiminius" wrote:
> >
> > > Hi John,
> > >
> > > let's try different attitude:
> > >
> > > dim i as long
> > > dim j as long
> > > dim rng as range
> > > set rng=range("b1..e1") ' the address of first row of data you want to
> > > print out
> > > j=0
> > > for i = 1 to rng.columns.count
> > > j=worksheetfunction.max(j,rng.cells(application.Rows.Count-rng.Row,1).end(xlup).row)
> > > next i
> > > activesheet.pagesetup.printarea =
> > > rng.Resize(j-rng.row+1,rng.Columns.Count).Address
> > >
> > > Change "b1..e1" in "set rng=range("b1..e1")" to be address of first row
> > > of data you want to print.
> > >
> > > Please let me know if it worked.
> > >
> > > Regards,
> > > Ivan
> > >
> > >

oops sorry - I meant Ivan's code

"JohnUK" wrote:

> Hi Tom,
> Thanks for your input.
> I wanted to print out some of the columns that contained formulas as well as
> the pivot table and I think the line that contains
> (application.Rows.Count-rng.Row,1) looks at the data in the first column
> (ideal because of no formulas) and gives me what I am looking for.
> However, I have tried to apply the same principle on a different page, but
> this time not so easy and I think it’s because all the columns have formulas.
> This is my poor way of trying to work around the problem:
> I am trying to use code to enter a value into a different column so that
> Toms code can do the same trick:
>
> Range("E40").Select
> If ActiveCell > 0 Then
> Range("N40").Select
> ActiveCell.FormulaR1C1 = "1"
>
> Range("E41").Select
> If ActiveCell > 0 Then
> Range("N41").Select
> ActiveCell.FormulaR1C1 = "1"
>
> Range("E42").Select
> If ActiveCell > 0 Then
> Range("N42").Select
> ActiveCell.FormulaR1C1 = "1"
>
> And so on and so on (I need 50 lines done this way)
> I know you must be laughing at me right now, but can you see what I am
> trying to do?
> May I ask for your help?
> Either there is another way altogether or can the above code be shortened
> somewhat?
>
> Regards
>
> John
>
>
> "Tom Ogilvy" wrote:
>
> > Using the example posted:
> >
> > Assume your pivotTable is in B1:D200 and you have formulas pre-entered in
> > E1:E1500
> >
> > this code only prints out B1:D200? Or are you not printing out the
> > pre-entered formulas? (Pivot Table actually in B1:E200 and formulas in
> > F1:F1500 as an example - then rng = Range("B1:E1"))
> >
> > Just curious - because I don't see how this solves the problem you described
> > if you want to include the pre-entered formulas.
> >
> > --
> > Regards,
> > Tom Ogilvy
> >
> >
> > "JohnUK" wrote:
> >
> > > Fantastic - Ivan you are a star.
> > >
> > > Many thanks - much appreciated - and thanks to Nick
> > >
> > > Take care
> > >
> > > Regards
> > >
> > > John
> > >
> > > "Ivan Raiminius" wrote:
> > >
> > > > Hi John,
> > > >
> > > > let's try different attitude:
> > > >
> > > > dim i as long
> > > > dim j as long
> > > > dim rng as range
> > > > set rng=range("b1..e1") ' the address of first row of data you want to
> > > > print out
> > > > j=0
> > > > for i = 1 to rng.columns.count
> > > > j=worksheetfunction.max(j,rng.cells(application.Rows.Count-rng.Row,1).end(xlup).row)
> > > > next i
> > > > activesheet.pagesetup.printarea =
> > > > rng.Resize(j-rng.row+1,rng.Columns.Count).Address
> > > >
> > > > Change "b1..e1" in "set rng=range("b1..e1")" to be address of first row
> > > > of data you want to print.
> > > >
> > > > Please let me know if it worked.
> > > >
> > > > Regards,
> > > > Ivan
> > > >
> > > >

Yes, your right. That whole loop ends up just giving you the used last row
in column A from what I can see. Not sure why the loop is even there.

--
Regards,
Tom Ogilvy


"JohnUK" wrote:

> Hi Tom,
> Thanks for your input.
> I wanted to print out some of the columns that contained formulas as well as
> the pivot table and I think the line that contains
> (application.Rows.Count-rng.Row,1) looks at the data in the first column
> (ideal because of no formulas) and gives me what I am looking for.
> However, I have tried to apply the same principle on a different page, but
> this time not so easy and I think it’s because all the columns have formulas.
> This is my poor way of trying to work around the problem:
> I am trying to use code to enter a value into a different column so that
> Toms code can do the same trick:
>
> Range("E40").Select
> If ActiveCell > 0 Then
> Range("N40").Select
> ActiveCell.FormulaR1C1 = "1"
>
> Range("E41").Select
> If ActiveCell > 0 Then
> Range("N41").Select
> ActiveCell.FormulaR1C1 = "1"
>
> Range("E42").Select
> If ActiveCell > 0 Then
> Range("N42").Select
> ActiveCell.FormulaR1C1 = "1"
>
> And so on and so on (I need 50 lines done this way)
> I know you must be laughing at me right now, but can you see what I am
> trying to do?
> May I ask for your help?
> Either there is another way altogether or can the above code be shortened
> somewhat?
>
> Regards
>
> John
>
>
> "Tom Ogilvy" wrote:
>
> > Using the example posted:
> >
> > Assume your pivotTable is in B1:D200 and you have formulas pre-entered in
> > E1:E1500
> >
> > this code only prints out B1:D200? Or are you not printing out the
> > pre-entered formulas? (Pivot Table actually in B1:E200 and formulas in
> > F1:F1500 as an example - then rng = Range("B1:E1"))
> >
> > Just curious - because I don't see how this solves the problem you described
> > if you want to include the pre-entered formulas.
> >
> > --
> > Regards,
> > Tom Ogilvy
> >
> >
> > "JohnUK" wrote:
> >
> > > Fantastic - Ivan you are a star.
> > >
> > > Many thanks - much appreciated - and thanks to Nick
> > >
> > > Take care
> > >
> > > Regards
> > >
> > > John
> > >
> > > "Ivan Raiminius" wrote:
> > >
> > > > Hi John,
> > > >
> > > > let's try different attitude:
> > > >
> > > > dim i as long
> > > > dim j as long
> > > > dim rng as range
> > > > set rng=range("b1..e1") ' the address of first row of data you want to
> > > > print out
> > > > j=0
> > > > for i = 1 to rng.columns.count
> > > > j=worksheetfunction.max(j,rng.cells(application.Rows.Count-rng.Row,1).end(xlup).row)
> > > > next i
> > > > activesheet.pagesetup.printarea =
> > > > rng.Resize(j-rng.row+1,rng.Columns.Count).Address
> > > >
> > > > Change "b1..e1" in "set rng=range("b1..e1")" to be address of first row
> > > > of data you want to print.
> > > >
> > > > Please let me know if it worked.
> > > >
> > > > Regards,
> > > > Ivan
> > > >
> > > >

Possibly
for i = 1 to 50
if cells(i + 39) > 0 then cells(i + 39,"N").Value = 1
Next


if you want to quit the first time the cell is not > 0 then

for i = 1 to 50
if cells(i + 39) > 0 then
cells(i + 39,"N").Value = 1
else
exit for
end if
Next

--
Regards,
Tom Ogilvy


"JohnUK" wrote:

> Hi Tom,
> Thanks for your input.
> I wanted to print out some of the columns that contained formulas as well as
> the pivot table and I think the line that contains
> (application.Rows.Count-rng.Row,1) looks at the data in the first column
> (ideal because of no formulas) and gives me what I am looking for.
> However, I have tried to apply the same principle on a different page, but
> this time not so easy and I think it’s because all the columns have formulas.
> This is my poor way of trying to work around the problem:
> I am trying to use code to enter a value into a different column so that
> Toms code can do the same trick:
>
> Range("E40").Select
> If ActiveCell > 0 Then
> Range("N40").Select
> ActiveCell.FormulaR1C1 = "1"
>
> Range("E41").Select
> If ActiveCell > 0 Then
> Range("N41").Select
> ActiveCell.FormulaR1C1 = "1"
>
> Range("E42").Select
> If ActiveCell > 0 Then
> Range("N42").Select
> ActiveCell.FormulaR1C1 = "1"
>
> And so on and so on (I need 50 lines done this way)
> I know you must be laughing at me right now, but can you see what I am
> trying to do?
> May I ask for your help?
> Either there is another way altogether or can the above code be shortened
> somewhat?
>
> Regards
>
> John
>
>
> "Tom Ogilvy" wrote:
>
> > Using the example posted:
> >
> > Assume your pivotTable is in B1:D200 and you have formulas pre-entered in
> > E1:E1500
> >
> > this code only prints out B1:D200? Or are you not printing out the
> > pre-entered formulas? (Pivot Table actually in B1:E200 and formulas in
> > F1:F1500 as an example - then rng = Range("B1:E1"))
> >
> > Just curious - because I don't see how this solves the problem you described
> > if you want to include the pre-entered formulas.
> >
> > --
> > Regards,
> > Tom Ogilvy
> >
> >
> > "JohnUK" wrote:
> >
> > > Fantastic - Ivan you are a star.
> > >
> > > Many thanks - much appreciated - and thanks to Nick
> > >
> > > Take care
> > >
> > > Regards
> > >
> > > John
> > >
> > > "Ivan Raiminius" wrote:
> > >
> > > > Hi John,
> > > >
> > > > let's try different attitude:
> > > >
> > > > dim i as long
> > > > dim j as long
> > > > dim rng as range
> > > > set rng=range("b1..e1") ' the address of first row of data you want to
> > > > print out
> > > > j=0
> > > > for i = 1 to rng.columns.count
> > > > j=worksheetfunction.max(j,rng.cells(application.Rows.Count-rng.Row,1).end(xlup).row)
> > > > next i
> > > > activesheet.pagesetup.printarea =
> > > > rng.Resize(j-rng.row+1,rng.Columns.Count).Address
> > > >
> > > > Change "b1..e1" in "set rng=range("b1..e1")" to be address of first row
> > > > of data you want to print.
> > > >
> > > > Please let me know if it worked.
> > > >
> > > > Regards,
> > > > Ivan
> > > >
> > > >