Filter List Box and combobox

[Pinkster69]

Hi Guys,

I downloaded sample code but I dont have the writers name to thank him, sorry about that.

Anyway I have a userform with a Combobox "D1" for searching suppliers and a Listbox2 to display all information pertaining to the supplier selected when I press on CommandButton1.
Everything is working as it should by displaying information in Listbox2 when the range is set to .Range("A1", .Range("A65536") in the UserForm_Initialize() sub, which is the wrong range I need as it displays dates and not the suppliers, but when I change the range in the "Private Sub UserForm_Initialize()" from .Range("A1", .Range("A65536") to .Range("C1", .Range("C65536") where my supplier list column is I only get the headings of the column only.

I have a feeling its something very simple that I am missing so if anyone could help me out with this it would be great.

Many thanks guys

Pinkster69

Private Sub UserForm_Initialize()


'Create unique sorted lists via AdvancedFilter
'Leave a blank column between lists so we can name the CurrentRegion
    With Sheet1
        'Clear range ready for unique lists
             Sheet2.Range("O1:AF100").ClearContents
       
        'Create Unique Description List
            .Range("A1", .Range("A65536").End(xlUp)).AdvancedFilter _
             Action:=xlFilterCopy, CriteriaRange:="", CopyToRange:=Sheet2.Range("O1"), Unique:=True
             Sheet2.Range("O1").CurrentRegion.Offset(1, 0).Name = "DescriptionList"
             Range("DescriptionList").Sort Key1:=Range("DescriptionList").Cells(1, 1), Order1:=xlAscending, Header:=xlNo
             D1.RowSource = "DescriptionList"
           
             
        
    End With
    
End Sub




Private Sub CommandButton1_Click()
'Dim strOperator1 As String, strOperator2 As String
Dim rCell As Range


    With Sheet2
             On Error Resume Next
             'Clear extract range and Criteria range
            .Range("CriteriaData").ClearContents
            .Range("Z1:AD100").Clear
            
            'Put in criteria as chosen
               
                    If D1.ListIndex > -1 Then .Range("B4") = "=" & """" & D1.Value & """"
                    
           
                 
            'Check if any criteria has been added
            If WorksheetFunction.CountA(Range("FirstRowCriteria")) > 0 Then
                    'Fill in needed blank cells
                        For Each rCell In Range("SecondRowCriteria")
                            If IsEmpty(rCell) And rCell.Offset(-1, 0) <> "" Then
                                rCell = rCell.Offset(-1, 0)
                            End If
                        Next rCell
                                
                        'Set the filter criteria range according to entries
                        'If WorksheetFunction.CountA(Range("SecondRowCriteria")) > 0 Then
                                '.Range(.Range("A4").End(xlToRight).Offset(-1, 0), _
                                '.Range("L5").End(xlToLeft)).Name = "FilterCriteria"
                        'Else
                                '.Range(.Range("A4").End(xlToRight).Offset(-1, 0), _
                                '.Range("L4").End(xlToLeft)).Name = "FilterCriteria"
                       ' End If
                         
                    'AdvancedFilter data by chosen criteria
                        Range("Data_Table_With_Heads").AdvancedFilter _
                        Action:=xlFilterCopy, CriteriaRange:=Range("FilterCriteria"), CopyToRange:=.Range("Z1")
                    'Name the newly created filtered table
                        .Range("Z1").CurrentRegion.Offset(1, 0).Name = "Filtered_Data"
                        ListBox2.RowSource = ""
                        ListBox2.RowSource = "Filtered_Data"
            End If
    End With


    On Error GoTo 0
End Sub