Help with VBA dropdownlist print macro & visible cells range

Mr Excelinator

New Member
Joined
Mar 4, 2014
Messages
37
Hello everyone

Hope someone can help. In a bit of a head banger & up against a deadline.

I have a print macro which prints out all of the items selected in a list box loaded into a user form. Works perfectly. Now I need it to only print out the visible range based on filter results. Tried & tried & am struggling to get it to work. Any help at all would be massively appreciated.

I've included the code. Do shout if you need any clarification on the code.
Many thanks in advance!


Code:
Sub PrintAll(RptToPrint As Worksheet)
Dim NoPtnrs As Integer, c As Integer
Dim prng As String, pFrm As Object, PtnrRng As Range
'PtnrRng is used to resize the name range PartnerList so the userform listbox _
    can be loaded without the blank spaces.
    
Set pFrm = New frmUpdPrint
    
prng = "PartnerList" ' Range with partners names for selection. Using pRng repeatedly _
                        is better than the more lengthy "PartnerList".
    
NoPtnrs = Range(prng).Rows.Count
Load pFrm
Set PtnrRng = Range(prng)
'.Offset(1, 0).Resize(Range(pRng).Rows.Count - 2, 1)
    With pFrm
        .btnPrintSlctd.Tag = "Y"
        .lstPartners.Clear
        'Remove For loop which loads the partners into the Userform listbox _
            and use the below line which loads them all at once.
        .lstPartners.List = PtnrRng.Value
        'The below error handler is there incase the user has set the listbox _
            selection to a blank field.
        On Error Resume Next
            .lstPartners.Selected(Range("myPtnrPrintIndex").Value - 2) = True
        On Error GoTo 0
        
        .StartUpPosition = 2
        .Show
   
'Code to select partners to print and print/update using selections
        If .btnPrintSlctd.Tag = "Y" Then
            For c = LBound(.lstPartners.List) To UBound(.lstPartners.List)
                If .lstPartners.Selected(c) Then
                    Range("myPtnrPrintIndex") = c + 1
                    RptToPrint.PrintOut
                End If
            Next c
        End If
    End With
    
Unload pFrm
Set pFrm = Nothing
Set PtnrRng = Nothing
End Sub
 

Some videos you may like

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.

Watch MrExcel Video

Forum statistics

Threads
1,122,894
Messages
5,598,724
Members
414,253
Latest member
MarieCo

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top