Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Print Only Visible Rows Using AutoFilter
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 -- Cheers, Ryan |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Print Only Visible Rows Using AutoFilter
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 -- Gary "RyanH" wrote in message ... 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 -- Cheers, Ryan |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Print Only Visible Rows Using AutoFilter
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 "Gary Keramidas" wrote: 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 -- Gary "RyanH" wrote in message ... 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 -- Cheers, Ryan |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Print Only Visible Rows Using AutoFilter
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 "RyanH" wrote: 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 "Gary Keramidas" wrote: 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 -- Gary "RyanH" wrote in message ... 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 -- Cheers, Ryan |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel copy only visible rows from autofilter | Excel Discussion (Misc queries) | |||
Error, when autofilter does not return visible rows | Excel Programming | |||
countif for only visible rows when combined with autofilter - possible? | Excel Discussion (Misc queries) | |||
AutoFilter: Loop Thru Visible Rows? | Excel Programming | |||
AutoFilter - which rows are currently visible? | Excel Programming |