Dim lrw as Long

lrw = Cells(Rows.COUNT, "A").End(xlUp).Row

will give you the last row used in column A.
Change A to what ever column will have the last entry

than lrw = lrw -1 <<< subtract what ever to get the row you want.
and set the print range to lrw...

you could also use

lrw = Cells(Rows.COUNT, "A").End(xlUp).Offset(-1,0).Row
to set it to 1 row above...
--
steveB

Remove "AYN" from email to respond
"scarlett1" wrote in
message ...

Hi, Ive just recorded a macro to perform a subtotal calculation, add a
page break and also set a print range.

This works well however as my spreadsheet will change next week if I
add an extra line in, I want to ammend the code in the macro to ensure
that

a) the page break is always above the very last line of the
subtotalling (ie between the last subtotal and the grand total and
b) that the print area is always set correctly, currently the macro is
showing absolute cell references.

Thanks in advance,

this is my first attempt at macro editing


--
scarlett1
------------------------------------------------------------------------
scarlett1's Profile:
http://www.excelforum.com/member.php...o&userid=17495
View this thread: http://www.excelforum.com/showthread...hreadid=389756