add end rows after suppressing prior rows
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!!
.
|