Here they are.
1st clears all
2nd fills CB's
3rd gets desired data
Code:Private Sub cmdReset_Click() 'clear the data cmbProducts.Clear cmbCustomerType.Clear cmbRegion.Clear With Sheets("View") .Visible = True .Range("dataSet").CurrentRegion.Offset(1).ClearContents End With End Sub Private Sub cmdUpdateDropDowns_Click() sn = Sheets("data").Range("C3:E472") For j = 1 To 3 With CreateObject("System.Collections.ArrayList") For jj = 1 To UBound(sn, 1) If sn(jj, j) <> vbNullString Then If Not .Contains(CStr(sn(jj, j))) Then .Add CStr((sn(jj, j))) End If Next .Sort Choose(j, cmbProducts, cmbRegion, cmbCustomerType).List = Application.Transpose(.toarray) End With Next End Sub Private Sub cmdShowData_Click() Dim rngTarget As Range, i As Integer Application.ScreenUpdating = False Sheets("View").Range("dataSet").CurrentRegion.Offset(1).ClearContents With Worksheets("data") Set rngTarget = .Range("A1:J" & .Cells(Rows.Count, 1).End(xlUp).Row) With rngTarget For i = 1 To 3 .AutoFilter i + 2, Choose(i, cmbProducts.Text, cmbRegion.Text, cmbCustomerType.Text) Next .Offset(1).SpecialCells(xlCellTypeVisible).Copy Sheets("View").Range("B13") .AutoFilter End With End With Application.ScreenUpdating = True End Sub




Reply With Quote
Bookmarks