CODEWITHSUNDEEP
excelexcel-formulavba
Kelsius
Kelsius

Reputation: 473

Filter table by list

I have a table where the 1st column is a list of names (in this example, list of food), and I need to sort the table by filtering this column with a list. In this case, I would need to sort the "Food" Table by either "Fruit" or "Dessert", so that if "Fruit" is selected in Cell "E1", the table would only show rows where the Food is "Apple", "Banana", or "Grapes".

I've tried doing dependent dropdown lists with the INDIRECT function of Excel/Vba, but that didn't work.

Is this even possible to do in Excel? If this needs to be done with VBA, how would I do this?

enter image description here

Upvotes: 1

Views: 275

Answers (2)

Kelsius
Kelsius

Reputation: 473

Here's the whole module that I ended up using.

 Sub FilterByArray()

    Dim ary As Variant, Idx As Long, Jdx As Long
    Dim numCols As Long, numRows As Long
    Dim food As String, year As String
    food = "Food"
    year = "2015"
    numNamedRanges = 0

    SetDataValidation food, year

    Sheets(food).Activate
    numCols = Sheets(food).Range("A2", Range("Z2").End(xlToLeft)).SpecialCells(xlCellTypeVisible).Cells.Count
    For Idx = 1 To numCols
        numRows = Sheets(food).Range(Cells(2, Idx).Address, Range(Cells(499, Idx).Address).End(xlUp)).SpecialCells(xlCellTypeVisible).Cells.Count
'           Add named ranges for 1 to numRows for each column in 1 to numCols
        ActiveWorkbook.Names.Add Name:=Cells(1, Idx).Value, RefersTo:="=" & Cells(2, Idx).Address & ":" & Cells(numRows + 1, Idx).Address
    Next Idx

    For Idx = 1 To numCols
        If Sheets(year).Cells(1, 6).Value = Sheets(food).Cells(1, Idx).Value Then
            numRows = Sheets(food).Range(Cells(2, Idx).Address, Range(Cells(499, Idx).Address).End(xlUp)).SpecialCells(xlCellTypeVisible).Cells.Count
            ReDim ary(1 To numRows)
            For Jdx = 1 To numRows
                ary(Jdx) = Cells(Jdx + 1, Idx).Value
            Next Jdx
            Sheets(year).ListObjects(1).Range.AutoFilter Field:=1, Criteria1:= _
                ary, Operator:=xlFilterValues
            Exit For
        End If
    Next Idx
    Sheets(year).Activate

End Sub

Sub SetDataValidation(food As String, year As String)

    Dim listArray As Variant, Idx As Long
    Dim numCols As Long

    Sheets(food).Activate
    numCols = Sheets(food).Range("A2", Range("Z2").End(xlToLeft)).SpecialCells(xlCellTypeVisible).Cells.Count

    ReDim listArray(1 To numCols)
    For Idx = 1 To numCols
        listArray(Idx) = Cells(1, Idx).Value
    Next Idx

    Sheets(year).Range("F1").Validation.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
        Operator:=xlBetween, Formula1:=Join(listArray, ",")

End Sub

Upvotes: 0

user3819867
user3819867

Reputation: 1118

If you insist on using VBA... 

Sub ert()

ListName = Range("F1") 'the filter cell, e.g. "Dessert"
ListNumerosity = Range(ListName).Cells.Count 'counts the numerosity of your list
Dim MyList() As String 'creates list
ReDim MyList(1 To ListNumerosity) 'sets numerosity of list
Dim rng As Range
For Each rng In Range(ListName) 'for each cell in your filter, e.g. "Dessert"
i = i + 1
    MyList(i) = rng 'collect the cell values  into a list
Next

    ActiveSheet.ListObjects("Táblázat3").Range.AutoFilter Field:=1, Criteria1:=MyList(), Operator:=xlFilterValues 'replace Táblázat3 with your tablename, does filtering to your list
End Sub

For further specs and f'd trials see my uploaded file.

Upvotes: 1

Related Questions