| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Set print area based on last row with a given value
I have a workbook with multiple sheets. One sheet has lots of data organized
into a standard number of columns but varying number of rows (well call this sheet DATA). One of the columns is for certain geographical zones. I have another sheet (well call MENU) that acts as a menu for the DATA sheet so that when a button is clicked with one of these zone names, a macro fires to filter the DATA sheet by the column with the zone names and then prints it. It works okay, but I spend a lot of time each month updating each individual macro (136 of them) so that the print area only goes down to the last row with that particular zone name (i.e. to avoid getting multiple blank pages or in some cases chopping the data off early). I would like to rewrite the macros so that it automatically adjusts the print area. So for example, if Im on the MENU page and I click the button for Zone 1, I want the macro to go to the DATA page, filter column 10 (J) for Zone 1 and set the print area to include the last row where Zone 1 appears in column J. Ive found multiple posts regarding print area, but none that fits my unique situation. I am including a copy of my current macro below Sheets("DATA").Select Selection.AutoFilter Field:=10, Criteria1:="Zone 1" With ActiveSheet.PageSetup .PrintTitleRows = "$5:$5" .PrintTitleColumns = "" End With ActiveSheet.PageSetup.PrintArea = "$A$5:$I$8591" With ActiveSheet.PageSetup .LeftHeader = "Zone 1" & Chr(10) & "Company ABC" .CenterHeader = "" .RightHeader = "" .LeftFooter = "" .CenterFooter = "" .RightFooter = "" .LeftMargin = Application.InchesToPoints(0.5) .RightMargin = Application.InchesToPoints(0.5) .TopMargin = Application.InchesToPoints(1) .BottomMargin = Application.InchesToPoints(0.5) .HeaderMargin = Application.InchesToPoints(0.5) .FooterMargin = Application.InchesToPoints(0.5) .PrintHeadings = False .PrintGridlines = True .PrintComments = xlPrintNoComments .PrintQuality = 600 .CenterHorizontally = True .CenterVertically = False .Orientation = xlPortrait .Draft = False .PaperSize = xlPaperLetter .FirstPageNumber = xlAutomatic .Order = xlDownThenOver .BlackAndWhite = False .Zoom = 50 .PrintErrors = xlPrintErrorsDisplayed End With End Sub |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Set print area based on last row with a given value
I think you can replace your current line that sets the print area with
these... LastRowInJ = ActiveSheet.Cells(Rows.Count, "J").End(xlUp).Row ActiveSheet.PageSetup.PrintArea = "$A$5:$I$" & LastRowInJ Don't forget to Dim the LastRowInJ variable. -- Rick (MVP - Excel) "jjones" wrote in message ... I have a workbook with multiple sheets. One sheet has lots of data organized into a standard number of columns but varying number of rows (well call this sheet DATA). One of the columns is for certain geographical zones. I have another sheet (well call MENU) that acts as a menu for the DATA sheet so that when a button is clicked with one of these zone names, a macro fires to filter the DATA sheet by the column with the zone names and then prints it. It works okay, but I spend a lot of time each month updating each individual macro (136 of them) so that the print area only goes down to the last row with that particular zone name (i.e. to avoid getting multiple blank pages or in some cases chopping the data off early). I would like to rewrite the macros so that it automatically adjusts the print area. So for example, if Im on the MENU page and I click the button for Zone 1, I want the macro to go to the DATA page, filter column 10 (J) for Zone 1 and set the print area to include the last row where Zone 1 appears in column J. Ive found multiple posts regarding print area, but none that fits my unique situation. I am including a copy of my current macro below Sheets("DATA").Select Selection.AutoFilter Field:=10, Criteria1:="Zone 1" With ActiveSheet.PageSetup .PrintTitleRows = "$5:$5" .PrintTitleColumns = "" End With ActiveSheet.PageSetup.PrintArea = "$A$5:$I$8591" With ActiveSheet.PageSetup .LeftHeader = "Zone 1" & Chr(10) & "Company ABC" .CenterHeader = "" .RightHeader = "" .LeftFooter = "" .CenterFooter = "" .RightFooter = "" .LeftMargin = Application.InchesToPoints(0.5) .RightMargin = Application.InchesToPoints(0.5) .TopMargin = Application.InchesToPoints(1) .BottomMargin = Application.InchesToPoints(0.5) .HeaderMargin = Application.InchesToPoints(0.5) .FooterMargin = Application.InchesToPoints(0.5) .PrintHeadings = False .PrintGridlines = True .PrintComments = xlPrintNoComments .PrintQuality = 600 .CenterHorizontally = True .CenterVertically = False .Orientation = xlPortrait .Draft = False .PaperSize = xlPaperLetter .FirstPageNumber = xlAutomatic .Order = xlDownThenOver .BlackAndWhite = False .Zoom = 50 .PrintErrors = xlPrintErrorsDisplayed End With End Sub |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Set print area based on last row with a given value
Sort of, but not exactly. I'm not looking just for the last row, I want the
last row that contains a particular cell value. So I may have 15,000 rows, but the last row with "Zone 1" may be on row 2,000. If the print area includes the last row (i.e. 15,000) then I'll get extra blank pages coming out of the printer for everything between 2,000 and 15,000. "Rick Rothstein" wrote: I think you can replace your current line that sets the print area with these... LastRowInJ = ActiveSheet.Cells(Rows.Count, "J").End(xlUp).Row ActiveSheet.PageSetup.PrintArea = "$A$5:$I$" & LastRowInJ Don't forget to Dim the LastRowInJ variable. -- Rick (MVP - Excel) "jjones" wrote in message ... I have a workbook with multiple sheets. One sheet has lots of data organized into a standard number of columns but varying number of rows (well call this sheet DATA). One of the columns is for certain geographical zones. I have another sheet (well call MENU) that acts as a menu for the DATA sheet so that when a button is clicked with one of these zone names, a macro fires to filter the DATA sheet by the column with the zone names and then prints it. It works okay, but I spend a lot of time each month updating each individual macro (136 of them) so that the print area only goes down to the last row with that particular zone name (i.e. to avoid getting multiple blank pages or in some cases chopping the data off early). I would like to rewrite the macros so that it automatically adjusts the print area. So for example, if Im on the MENU page and I click the button for Zone 1, I want the macro to go to the DATA page, filter column 10 (J) for Zone 1 and set the print area to include the last row where Zone 1 appears in column J. Ive found multiple posts regarding print area, but none that fits my unique situation. I am including a copy of my current macro below Sheets("DATA").Select Selection.AutoFilter Field:=10, Criteria1:="Zone 1" With ActiveSheet.PageSetup .PrintTitleRows = "$5:$5" .PrintTitleColumns = "" End With ActiveSheet.PageSetup.PrintArea = "$A$5:$I$8591" With ActiveSheet.PageSetup .LeftHeader = "Zone 1" & Chr(10) & "Company ABC" .CenterHeader = "" .RightHeader = "" .LeftFooter = "" .CenterFooter = "" .RightFooter = "" .LeftMargin = Application.InchesToPoints(0.5) .RightMargin = Application.InchesToPoints(0.5) .TopMargin = Application.InchesToPoints(1) .BottomMargin = Application.InchesToPoints(0.5) .HeaderMargin = Application.InchesToPoints(0.5) .FooterMargin = Application.InchesToPoints(0.5) .PrintHeadings = False .PrintGridlines = True .PrintComments = xlPrintNoComments .PrintQuality = 600 .CenterHorizontally = True .CenterVertically = False .Orientation = xlPortrait .Draft = False .PaperSize = xlPaperLetter .FirstPageNumber = xlAutomatic .Order = xlDownThenOver .BlackAndWhite = False .Zoom = 50 .PrintErrors = xlPrintErrorsDisplayed End With End Sub |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Set print area based on last row with a given value
Unless I am completely mistaken, after you apply the AutoFilter method
(which I would do to Columns("J") rather than Selection by the way), the calculated LastRowInJ will be for the last visible filtered row (which will be the last row containing "Zone 1" in your example code). Try what I posted... I am pretty sure it will do what you asked for. -- Rick (MVP - Excel) "jjones" wrote in message ... Sort of, but not exactly. I'm not looking just for the last row, I want the last row that contains a particular cell value. So I may have 15,000 rows, but the last row with "Zone 1" may be on row 2,000. If the print area includes the last row (i.e. 15,000) then I'll get extra blank pages coming out of the printer for everything between 2,000 and 15,000. "Rick Rothstein" wrote: I think you can replace your current line that sets the print area with these... LastRowInJ = ActiveSheet.Cells(Rows.Count, "J").End(xlUp).Row ActiveSheet.PageSetup.PrintArea = "$A$5:$I$" & LastRowInJ Don't forget to Dim the LastRowInJ variable. -- Rick (MVP - Excel) "jjones" wrote in message ... I have a workbook with multiple sheets. One sheet has lots of data organized into a standard number of columns but varying number of rows (well call this sheet DATA). One of the columns is for certain geographical zones. I have another sheet (well call MENU) that acts as a menu for the DATA sheet so that when a button is clicked with one of these zone names, a macro fires to filter the DATA sheet by the column with the zone names and then prints it. It works okay, but I spend a lot of time each month updating each individual macro (136 of them) so that the print area only goes down to the last row with that particular zone name (i.e. to avoid getting multiple blank pages or in some cases chopping the data off early). I would like to rewrite the macros so that it automatically adjusts the print area. So for example, if Im on the MENU page and I click the button for Zone 1, I want the macro to go to the DATA page, filter column 10 (J) for Zone 1 and set the area to include the last row where Zone 1 appears in column J. Ive found multiple posts regarding print area, but none that fits my unique situation. I am including a copy of my current macro below Sheets("DATA").Select Selection.AutoFilter Field:=10, Criteria1:="Zone 1" With ActiveSheet.PageSetup .PrintTitleRows = "$5:$5" .PrintTitleColumns = "" End With ActiveSheet.PageSetup.PrintArea = "$A$5:$I$8591" With ActiveSheet.PageSetup .LeftHeader = "Zone 1" & Chr(10) & "Company ABC" .CenterHeader = "" .RightHeader = "" .LeftFooter = "" .CenterFooter = "" .RightFooter = "" .LeftMargin = Application.InchesToPoints(0.5) .RightMargin = Application.InchesToPoints(0.5) .TopMargin = Application.InchesToPoints(1) .BottomMargin = Application.InchesToPoints(0.5) .HeaderMargin = Application.InchesToPoints(0.5) .FooterMargin = Application.InchesToPoints(0.5) .PrintHeadings = False .PrintGridlines = True .PrintComments = xlPrintNoComments .PrintQuality = 600 .CenterHorizontally = True .CenterVertically = False .Orientation = xlPortrait .Draft = False .PaperSize = xlPaperLetter .FirstPageNumber = xlAutomatic .Order = xlDownThenOver .BlackAndWhite = False .Zoom = 50 .PrintErrors = xlPrintErrorsDisplayed End With End Sub |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Predefined Print area based on variable | Excel Programming | |||
| Set then print area based on a search | Excel Programming | |||
| dynamic print area based on cell value | Excel Worksheet Functions | |||
| set print area based on selection | Excel Programming | |||
| set print area based on selection | Excel Programming | |||
Linear Mode
