Hi Ryan,
I don't see a print area set. Has it been set in the interactive mode? I
suspect that cells outside the actual filtered range are getting included.
Try setting Print Area to the entire range to be filtered as follows.
Set rngFilterRange = Sheets("Global Schedule").UsedRange
Sheets("Global Schedule").PageSetup.PrintArea = rngFilterRange.Address
Another thing to be aware of is that UsedRange can include additional
rows/columns; especially if you have some formatting in the otherwise unused
cells. You can test this with the following code.
Msgbox ActiveSheet.UsedRange.Address
Then select some rows below the used range and format them slightly wider
and re-run the test. Re-format them back to standard and run the test again
and you might find that they are still shown to be in the used range.
--
Regards,
OssieMac
"RyanH" wrote:
[Quoted Text] > I have a large list of products that are in production on a worksheet. Each
> product has its own row and an associated 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 from a listbox, 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 listbox 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 filtered list, right?
> 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
>
> --
> Cheers,
> Ryan
|