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