Set print area based on last row with a given value

[jjones]

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 (we’ll call this
sheet DATA). One of the columns is for certain geographical zones. I have
another sheet (we’ll 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
I’m 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. I’ve 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

 

[Rick Rothstein]

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.

 

[jjones]

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]

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.