add end rows after suppressing prior rows
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!! |
add end rows after suppressing prior rows
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! |
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!! . |
All times are GMT +1. The time now is 01:43 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com