VBA: Filter table data based on drop down selection

Question

I would like to filter a table Column C, based on a drop down list selection. I have more lines, where I can select Country code form the drop down list.

I would like to filter my table based on the country code selection.

For example:

First line: "54" country code selected

Second line "24" country code selected

And so on....

The table on the other tab will be filtered by the selected country code "54","24".

Can you please help me how can I manage it?

Thank you :)

Have you tried to make your own VBA solution? Please show us what you have tried so far, and what part of that attempt has failed. The more effort you put in, the more people here will be willing to help you. — Grade 'Eh' Bacon
Would it be too much to just set your table's cells to like =If(A1=54,[data to show for 54],[data to show if 24]). — BruceWayne
Sub FilterRangeCriteria() Dim vCrit As Variant Dim wsFiltered As Worksheet Dim wsSelection As Worksheet Dim rngCrit As Range Dim rngOrders As Range Dim Lastrow As Integer Set wsFiltered = Worksheets("S") ' I want to filter this tab with "Centre Information" selection Set wsSelection = Worksheets("Centre Information") Set rngOrders = wsFiltered.Range("b:b") 'I want to filter this column Lastrow = Worksheets("Centre Information").Cells(Rows.Count, 2).End(xlUp).Row myrange = ("b3:b" & Lastrow) ' the value from B3 until last row: this will be the filter data Set rngCrit = wsSelection.Range(myrange — Erika

psychicebola psychicebola · Accepted Answer · 2015-07-24T10:28:33

Sub FilterRangeCriteria()
Dim vCrit As Variant
Dim wsFiltered As Worksheet
Dim wsSelection As Worksheet
Dim rngCrit As Range
Dim rngOrders As Range
Dim Lastrow As Integer

'you need more variables to save the range in an array
Dim valArr As Variant
Dim cl As Range
Dim i As Integer

Set wsFiltered = Worksheets("S") ' I want to filter this tab with "Centre   Information" selection
Set wsSelection = Worksheets("Centre Information")
Set rngOrders = wsFiltered.Range("b:b") 'I want to filter this column

Lastrow = Worksheets("Centre Information").Cells(Rows.Count, 2).End(xlUp).Row
myrange = ("b3:b" & Lastrow) ' the value from B3 until last row: this will be the filter data
Set rngCrit = wsSelection.Range(myrange)

vCrit = rngCrit.Value

'I get error here: Autofilter method of range class failed

'Correction: Fill array
ReDim valArr(Lastrow - 3) 'define array size (first two rows are empty + considering the first array position starts with 0)
i = 0
For Each cl In rngCrit 'loop through range
    valArr(i) = "=" & cl 'filter for each value + operator
    i = i + 1
Next cl

'Correction: use array als range of numbers which shall be matched
rngOrders.AutoFilter _
    Field:=1, _
    Criteria1:=valArr, _
    Operator:=xlFilterValues

End Sub

Comment: If you want to filter for a range, using autofilter you have provide an array which contains all values in string format. Filtering numbers requires an operator: e.g. "=", "<=", etc. Best regards.

VBA: Filter table data based on drop down selection

1 Answers