Printing Filtered Data

[ExcelNoviceGA]

I've got a spreadsheet which I'm trying to filter and print and have fun into some issues. I've gotten the form filtered and it prints but only the first 2 filtered rows. I've pasted the code below but my "goal" is as follows (in case my garbled code doesn't reflect that).

The button, once pushed, will filter a spreadsheet based on a specific column and will then print columns A-W down to a relative row. I'm using column "H" to attempt to determine the last row because it has data in every cell (not all the columns do). Thanks for any help offered.

Private Sub TestPrint_Click()
Worksheets("2011 MPL").AutoFilterMode = False

Sheets("2011 MPL").Activate

Worksheets("2011 MPL").Range("A1").End(xlToLeft).AutoFilter _
field:=8, _
Criteria1:="GZ", _
VisibleDropDown:=True


StartCol = 1
startRow = 1
EndCol = 23
EndRow = Range("H2").SpecialCells(xlCellTypeLastCell).Row


With Worksheets("2011 MPL")
.PageSetup.PrintArea = .Range(.Cells(startRow, StartCol), .Cells(EndRow, EndCol)).Address(1, 1, xlA1, True)
.PageSetup.PaperSize = xlPaperLedger
.PageSetup.Orientation = xlPortrait
.PageSetup.FitToPagesWide = 1
.PageSetup.Zoom = False
End With

ActiveSheet.PrintOut

End Sub

 

[ukmikeb]

Hi

Try -

Dim Prrng as String
 
Prrng = "A1:H" & Endrow

then change the setup to -

.PageSetup.PrintArea = Prrng

hth

 

[ExcelNoviceGA]

Still yielding the same results. It prints the first 2 filtered rows which correspond to rows 19 & 21. I've noticed that there seems to be a few different ways (each with their own drawbacks or disadvantages) to find the last row and I'm wondering if there is something wrong with how I'm doing that.

 

[ukmikeb]

Hi

The following will provide you with the area that has been autofiltered -

Prrng = ActiveSheet.AutoFilter.Range.Address

and then for the PageSetup -

.PageSetup.PrintArea = .Range(Prrng).SpecialCells(xlCellTypeVisible)

If you need to resize it columnwise use -

.PageSetup.PrintArea = .Range(Prrng).Resize(,23).SpecialCells(xlCellTypeVisible)

hth

 

[ExcelNoviceGA]

Issue resolved, thanks for your help!