Ftyjtk

Basically, the function of the comboboxes will be similar to a filter criteria. So every time I change the value of the combobox, the AfterUpdate event will run a code that should update the listbox to only contain values that match that criteria.

My plan was to use Autofilter to actually do this then use SpecialCells(xlCellTypeVisible) to set the rowsource.

Initial Code Idea:

Private Sub cbxSupplier_AfterUpdate()

Dim Suppler_col As Long

    Supplier_col = ThisWorkbook.Worksheets("Foil Profile").ListObjects("tblFoilProfile").ListColumns("SUPPLIER").Index

    filterRangeFoilProfile.AutoFilter Field:=Supplier_col, Criteria1:=cbxSupplier.Text

    lbxFoilInfoDisplay.RowSource = ThisWorkbook.Worksheets("Foil Profile").ListObjects("tblFoilProfile").Range.SpecialCells(xlCellTypeVisible)

End Sub

However, my issue is that the Rowsource method uses a string assuming an excel formula reference such as the following: lbxFoilInfoDisplay.RowSource ="'Foil Profile'!A1:K5" and you cannot use VBA language to assign the values of the rowsource method.

The issue here is that even the values in between the filtered range will be used in the Rowsource. The work around here is to copy in a "Help worksheet" the visible cells of a table only by using SpecialCells(xlCellTypeVisible) instead, but my concern is that i'll need to clear the entire help worksheet in the After Update event for each time a combobox's value was changed.

Can I ask if there is any faster/easier/more efficient way to actually do this task?