Filtering a column of a table according several values in an array.

[mahhdy]

HI,
I want to filter all my table rows, according to several values for its one column which is set out in another sheet.
I want this code to be dynamic about the name of the table or the array of cells I want to filter according to their data. For instance, I want to select the array first, I input the name of the table in an input box then I see the results. Tnx

 

[My Aswer Is This]

So if I understand you want to:
1. Filter by Table1 or Table2 depending on what Table Name you enter into a Input box.
2. And you will select a range of values you want to filter the sheet by.
3. For example it you select the Range ("A1:A3") which has the values "Mom" "Dad" "Brother"

The Table will be filtered by these values.

Is that what you want?

If the answer is "Yes" then use this script.
The script always filters by Field2 of the Table
You did not mention making the field dynamic.
You can change this in the script or we will need a Input box to make the field Dynamic.

Sub Filter_Me()
Dim Criteria_Val(100) As String
Dim i As Integer
Dim ans As String
ans = InputBox("Enter Table Name")
    With Range(ans)
        For i = 0 To Selection.Count
            Criteria_Val(i) = Selection(i)
        Next
    End With
    ActiveSheet.Range(ans).AutoFilter Field:=2, Criteria1:=Criteria_Val, Operator:=xlFilterValues
End Sub

 

[mahhdy]

Thanks for your answer,
It worked Excellent.
Just one more question, If the table is in another worksheet, I should use switch window yes? or the code have to change more. Yours,
M

So if I understand you want to:
1. Filter by Table1 or Table2 depending on what Table Name you enter into a Input box.
2. And you will select a range of values you want to filter the sheet by.
3. For example it you select the Range ("A1:A3") which has the values "Mom" "Dad" "Brother"

The Table will be filtered by these values.

Is that what you want?

If the answer is "Yes" then use this script.
The script always filters by Field2 of the Table
You did not mention making the field dynamic.
You can change this in the script or we will need a Input box to make the field Dynamic.

Sub Filter_Me()
Dim Criteria_Val(100) As String
Dim i As Integer
Dim ans As String
ans = InputBox("Enter Table Name")
    With Range(ans)
        For i = 0 To Selection.Count
            Criteria_Val(i) = Selection(i)
        Next
    End With
    ActiveSheet.Range(ans).AutoFilter Field:=2, Criteria1:=Criteria_Val, Operator:=xlFilterValues
End Sub

 

[mahhdy]

I tried this code for :
1) removing the filter from the fields at the first step.
2) enabling the choose of column

but it is not working. It doesn't accept the a as column number.

Sub Filter_Me()Dim Criteria_Val(100) As String
Dim i As Integer
Dim a As Integer
Dim c As Integer ' for choosing the criteria
Dim ans As String
ans = InputBox("Enter Table Name")
    For a = 1 To 10
        ActiveSheet.Range(ans).AutoFilter Field:=a
    Next
c = InputBox("Enter column number")
    With Range(ans)
        For i = 0 To Selection.Count
            Criteria_Val(i) = Selection(i)
        Next
    End With
    ActiveSheet.Range(ans).AutoFilter Field:=c, Criteria1:=Criteria_Val, Operator:=xlFilterValues
End Sub

 

[My Aswer Is This]

Try this to provide for clear filter at start. Not sure why you need that but I added it. You will never see the result I do not think since we clear a filter then instantly filter again. But here it is. Or are you saying you want to clear all filters on all tables at the beginning and then want to filter a different table.

As far as switching sheets I'm not sure what your asking for.
Are you saying the Table is on one sheet and you want the selection of filter values to be on another sheet?

If that's so you must be adding this portion of script to another script. If that is so I would want to see the entire script.

Sub Filter_Me()
Dim Criteria_Val(100) As String
Dim i As Integer
Dim ans As String
ans = InputBox("Enter Table Name")
    With Range(ans)
        .AutoFilter
        
            For i = 1 To Selection.Count
            Criteria_Val(i) = Selection(i)
        Next
    End With
    ActiveSheet.Range(ans).AutoFilter Field:=2, Criteria1:=Criteria_Val, Operator:=xlFilterValues
End Sub

 

[My Aswer Is This]

If you want all filters removed from all Tables in the active sheet or the entire workbook you need to let me know.

This script will do it for the active sheet.

Sub Clear_Filters()
Dim c As ListObject
    
    For Each c In ActiveSheet.ListObjects
      If c.Range.AutoFilter = True Then c.Range.AutoFilter
    Next
End Sub

 

[mahhdy]

Hi Again,
How can I filter the result when I just put some part of the values, can I use asterix? "%" or "*" for this porpuse in the codes? Can some one please help.
yours,
M

Sub Filter_Me()
Dim Criteria_Val(100) As String
Dim i As Integer
Dim ans As range
            For i = 1 To Selection.Count
            Criteria_Val(i) = Selection(i)
        Next
    End With
    ActiveSheet.Range(ans).AutoFilter Field:=2, Criteria1:=Criteria_Val, Operator:=xlFilterValues
End Sub

If it is hard for multiple entry filter. I am ok with single entry solution too.
Cheers,
M