Hi all,
I've got a worksheet designed to do estimates (for
products). It's set-up for the largest possible number
of items included in a quote for calculation purposes.
The end users are sales people, with end viewer being the
customer.

Sheet is 102 rows, product lines run rows 15-98, totals
run lines 99-102. GOAL - automatically suppress unused
lines between rows 15-98.

I've currently got a button set-up (use me to print
nice!) for the sales people to click, which uses the
following (thank you Tom!):

Sub suppressrows()
Dim pntrng As Range

lastrow = ActiveSheet.Range("a102").End(xlUp).Row
If ActiveSheet.Name = "Estimate_test2" Then
Set pntrng = Range("a1").CurrentRegion
lastcol = pntrng.Columns.Count
Else
lastcol = 11
End If
ActiveSheet.PageSetup.PrintArea = Range(Cells(1, 1), Cells
(lastrow + 1, lastcol)).Address
End Sub

This works beautifully in suppressing product line rows
not used. Problem is it also suppresses totals (pre-set
to do calculations). How can I add the info that appears
in rows 99-102 back into the printarea (moving it to the
end of the used product lines/rows)??

FYI - just suppressing all blank rows throughout document
is not an option (had a nice little macro for that!).
Moving totals to top of page is not an option either.

Seems like this should be easy, but I've spent hours
trying to figure it out. Any and all suggestions are
greatly appreciated. Thanks!!

Hi

What your code does is set up the area to be printed (the PrintArea); it doesn't actually print it, so you must have some more code as well. Without seeing that, and your data, it's hard to really understand why it does what it does, but I'll make a couple of assumptions

1. Your totals in rows 99-102 do not contain any data in Column A

2. What you want printed is from row 1 down as far as your data goes
AND a blank row AND rows 99 to 10

One way to do this is to hide the rows you don't want, while you print, and then unhide them afterwards, because hidden rows are not printed

' Find last data row and column - your existing cod
lastrow = ActiveSheet.Range("a102").End(xlUp).Ro
If ActiveSheet.Name = "Estimate_test2" The
Set pntrng = Range("a1").CurrentRegio
lastcol = pntrng.Columns.Coun
Els
lastcol = 1
End I

' Set up print area - always to row 10
ActiveSheet.PageSetup.PrintArea = Range(Cells(1, 1), Cells(102, lastcol)).Addres

' Hide rows (lastrow + 2) to 98 - if an
If lastrow < 96 the
ActiveSheet.Range(Cells(lastrow + 2, 1), Cells(97, 1)).EntireRow.Hidden = Tru
End I

' NOW DO YOUR PRINTING HER

' Unhide the rows agai
If lastrow < 96 the
ActiveSheet.Range(Cells(lastrow + 2, 1), Cells(97, 1)).EntireRow.Hidden = Fals
End I

Enjoy
Ton

----- Erin wrote: ----

Hi all
I've got a worksheet designed to do estimates (for
products). It's set-up for the largest possible number
of items included in a quote for calculation purposes.
The end users are sales people, with end viewer being the
customer

Sheet is 102 rows, product lines run rows 15-98, totals
run lines 99-102. GOAL - automatically suppress unused
lines between rows 15-98

I've currently got a button set-up (use me to print
nice!) for the sales people to click, which uses the
following (thank you Tom!)

Sub suppressrows(
Dim pntrng As Rang

lastrow = ActiveSheet.Range("a102").End(xlUp).Ro
If ActiveSheet.Name = "Estimate_test2" The
Set pntrng = Range("a1").CurrentRegio
lastcol = pntrng.Columns.Coun
Els
lastcol = 1
End I
ActiveSheet.PageSetup.PrintArea = Range(Cells(1, 1), Cell
(lastrow + 1, lastcol)).Addres
End Su

This works beautifully in suppressing product line rows
not used. Problem is it also suppresses totals (pre-set
to do calculations). How can I add the info that appears
in rows 99-102 back into the printarea (moving it to the
end of the used product lines/rows)?

FYI - just suppressing all blank rows throughout document
is not an option (had a nice little macro for that!).
Moving totals to top of page is not an option either

Seems like this should be easy, but I've spent hours
trying to figure it out. Any and all suggestions are
greatly appreciated. Thanks!

Tony,
Thanks much! That worked perfectly. I'm the office
hero :-)

-----Original Message-----
Hi,

What your code does is set up the area to be printed
(the PrintArea); it doesn't actually print it, so you
must have some more code as well. Without seeing that,
and your data, it's hard to really understand why it does
what it does, but I'll make a couple of assumptions:

1. Your totals in rows 99-102 do not contain any data
in Column A.

2. What you want printed is from row 1 down as far as
your data goes
AND a blank row AND rows 99 to 102

One way to do this is to hide the rows you don't want,
while you print, and then unhide them afterwards, because
hidden rows are not printed.

' Find last data row and column - your existing code
lastrow = ActiveSheet.Range("a102").End(xlUp).Row
If ActiveSheet.Name = "Estimate_test2" Then
Set pntrng = Range("a1").CurrentRegion
lastcol = pntrng.Columns.Count
Else
lastcol = 11
End If

' Set up print area - always to row 102
ActiveSheet.PageSetup.PrintArea = Range(Cells(1, 1),
Cells(102, lastcol)).Address

' Hide rows (lastrow + 2) to 98 - if any
If lastrow < 96 then
ActiveSheet.Range(Cells(lastrow + 2, 1), Cells(97,
1)).EntireRow.Hidden = True
End If

' NOW DO YOUR PRINTING HERE

' Unhide the rows again
If lastrow < 96 then
ActiveSheet.Range(Cells(lastrow + 2, 1), Cells(97,
1)).EntireRow.Hidden = False
End If

Enjoy,
Tony

----- Erin wrote: -----

Hi all,
I've got a worksheet designed to do estimates (for
products). It's set-up for the largest possible
number
of items included in a quote for calculation
purposes.
The end users are sales people, with end viewer
being the
customer.

Sheet is 102 rows, product lines run rows 15-98,
totals
run lines 99-102. GOAL - automatically suppress
unused
lines between rows 15-98.

I've currently got a button set-up (use me to print
nice!) for the sales people to click, which uses
the
following (thank you Tom!):

Sub suppressrows()
Dim pntrng As Range

lastrow = ActiveSheet.Range("a102").End(xlUp).Row
If ActiveSheet.Name = "Estimate_test2" Then
Set pntrng = Range("a1").CurrentRegion
lastcol = pntrng.Columns.Count
Else
lastcol = 11
End If
ActiveSheet.PageSetup.PrintArea = Range(Cells(1,
1), Cells
(lastrow + 1, lastcol)).Address
End Sub

This works beautifully in suppressing product line
rows
not used. Problem is it also suppresses totals
(pre-set
to do calculations). How can I add the info that
appears
in rows 99-102 back into the printarea (moving it
to the
end of the used product lines/rows)??

FYI - just suppressing all blank rows throughout
document
is not an option (had a nice little macro for
that!).
Moving totals to top of page is not an option
either.

Seems like this should be easy, but I've spent
hours
trying to figure it out. Any and all suggestions
are
greatly appreciated. Thanks!!

.