Advanced Filter With Drop Down Lists

Excel VBA Advanced Filter With Drop Down Lists

        In this example ,we have used to filter another sheet . We have used drop-down lists (Data_Validation_List) to facilitate filtering.

          The drop-down lists are filled with unique values. Also named ranges are used as sources of data validation (Col1,Col2,Col3 etc.)

When there are changes in the data sheet, may be necessary updating the unique values. so when the book is opened ,"unique values" macro runs automatically.

Private Sub Workbook_Open()
Call unique_values
End Sub

Or unique values can be updated by pressing the button on the "Unique Values" page.

Any updating doesn't need for the named ranges  .Because the codes of named ranges were defined accordingly.For example :

ActiveWorkbook.Names.Add Name:="Col1", RefersTo:="=OFFSET(Unique_Values!$A$2,0,0,COUNTA(Unique_Values!$A:$A)-0)"

 The drop-down lists will have updated values with no additional effort!