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" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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.Ro ws.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.Ro ws.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.Ro ws.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 its 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.Ro ws.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 its 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.Ro ws.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 its 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.Ro ws.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 its 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.Ro ws.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