Print Only Visible Rows Using AutoFilter

[RyanH]

I have a large list of products that are in production on a worksheet. Each
product is associated by a sales person intials which is located in Col. C.
I have a userform that contains a listbox of the sales persons intials. When
the user wants to print all the products associated by a particular sales
person, the userform is called, select the sales person, then use autofilter
to hide all other sales people, then print that persons list.

Problem: Say there is a total of 5 Sales People on the worksheet. If the
user only selects 1 Sales Person from the list it prints that persons
filtered product list, but then also prints 4 blank sheets with just the
header, why? My code is only telling it to print the selected list. Any
ideas?

Private Sub btnPrint_Click()

Dim rngFilterRange As Range
Dim i As Integer

Set rngFilterRange = Sheets("Global Schedule").UsedRange

With lboSalesPeople
For i = 0 To .ListCount - 1
If .Selected(i) Then
rngFilterRange.AutoFilter Field:=3, Criteria1:=.List(i),
VisibleDropDown:=False
ActiveSheet.PrintOut Copies:=1, Collate:=True
End If
Next i
End With

rngFilterRange.AutoFilter

End Sub

 

[Gary Keramidas]

maybe add a test to see if there are and visible rows:

If .Columns(1).SpecialCells(xlCellTypeVisible).Cells.Count > 1 Then
ActiveSheet.PrintOut Copies:=1, Collate:=True
end if

 

[RyanH]

Thanks for the reply Gary. Unfortunately I am getting the same result. For
whatever reason it is printing the selected sales persons schedule great, but
prints only the header row for all the non selected sales people. It should
not print anything if the sales person isn't selected in the list box. Any
other ideas?
--
Cheers,
Ryan

maybe add a test to see if there are and visible rows:

If .Columns(1).SpecialCells(xlCellTypeVisible).Cells.Count > 1 Then
ActiveSheet.PrintOut Copies:=1, Collate:=True
end if

 

[RyanH]

This is a little work around that I did. Any ideas to improve this code to
make it faster or less code to read.

With lboSalesPeople
For i = 0 To .ListCount - 1
If .Selected(i) Then

' see if the sales person selected is on the schedule
Set rngSalesPerson = Sheets("Global
Schedule").Range("C:C").Find(What:=.List(i), LookIn:=xlValues,
LookAt:=xlWhole)

If Not rngSalesPerson Is Nothing Then

' first row with selected sales person
lngLastRow = rngSalesPerson.Row

' find last row of selected sales person
Do Until Cells(lngLastRow, "C").Offset(1, 0) <> .List(i)
lngLastRow = lngLastRow + 1
Loop

' print sales persons schedule
Range(Cells(rngSalesPerson.Row, "A"), Cells(lngLastRow,
"M")).PrintOut
Else
' tell user that the select sales person has no schedule
to print
strPrompt = .List(i) & " has no items on the schedule.
Click Ok to continue."
intButtons = vbExclamation
strTitle = "Sales Schedule"
MsgBox strPrompt, intButtons, strTitle
End If
End If
Next i
End With
--
Cheers,
Ryan

Thanks for the reply Gary. Unfortunately I am getting the same result. For
whatever reason it is printing the selected sales persons schedule great, but
prints only the header row for all the non selected sales people. It should
not print anything if the sales person isn't selected in the list box. Any
other ideas?