Microsoft Office Forums - View Single Post - How to filter data before it is pulled into combobox/listbox AND remove data from listbox

NoSparks · 05-14-2019, 07:41 AM

For point 2, after determining arrUniqueCompanies you could try this to determine the latest date and what goes into the 'Daily List'

Code:

' Find latest 'date to be called' by filtering table and using subtotal
With Sheets("Data").ListObjects("tblMain").Range
    For i = LBound(arrUniqueCompanies) To UBound(arrUniqueCompanies)
            company = arrUniqueCompanies(i)
            'remove any existing filters
            .AutoFilter
            'filter for company
            .AutoFilter Field:=4, Criteria1:=company, Operator:=xlFilterValues
            'the latest date
            dateToBeCalled = WorksheetFunction.Subtotal(104, Sheets("Data").ListObjects("tblMain").ListColumns(8).DataBodyRange)
            'Populate the 'Daily Call' list box
            If dateToBeCalled <= Date Then
                DailyList.AddItem arrUniqueCompanies(i)
                DailyList.List(i, 1) = Format(dateToBeCalled, "dd-mmm-yy")
            End If
            'remove filter
            .AutoFilter
    Next i
End With