How to construct a multiple search criteria?

[inddon]

Hello There,

I have the below code used for only one column search criteria:

LOVMstTableColumn1 = "Age"
LOVSearchValue1 = "2"
if LOVMstTableColumn1 <> "" Then
  .Range.AutoFilter Field:=.ListColumns(LOVMstTableColumn1).Index, Criteria1:="=*" &     LOVSearchValue1 & "*"
End if

I would like to include more than one column in the search criteria.
LOVMstTableColumn2
LOVMstTableColumn3
'---
LOVSearchValue2
LOVSearchValue3


Could you please advise, how this can be done?

Thank you & regards,
Don

 

[Belleke]

I've been looking in my glass ball but I can't find LOVMst, I am not a fortune teller Please provide a example.

 

[inddon]

I've been looking in my glass ball but I can't find LOVMst, I am not a fortune teller Please provide a example.

hahaha, good one. . My apologies

These are just local variables for the excel table. There is an excel table whose name is "Tbl_Student"

Sub FilterData
  Dim LOVMstTableColumn1, LOVMstTableColumn2, LOVMstTableColumn3 as String
  Dim LOVSearchValue1, LOVSearchValue2, LOVSearchValue3 as String

  'Column names of Table "Tbl_Student"
  LOVMstTableColumn1 = "Name"
  LOVMstTableColumn2 = "Gender"
  LOVMstTableColumn3 = "Age"

  'Respective values for the above Table Column names
  LOVSearchValue1 = "Don"
  LOVSearchValue2 = "Male"  
  LOVSearchValue3 = "25"

  'The below code is the current filter code which is based on just one criteria
  'column LOVMstTableColumn1 and it's value LOVSearchValue1

  'I would like to include the above 3 Columns (LOVMstTableColumn1..3) and it's values (LOVSearchValue1..3)

  If LOVMstTableColumn1 <> "" Then
    .Range.AutoFilter Field:=.ListColumns(LOVMstTableColumn1).Index, Criteria1:="=*" &     LOVSearchValue1 & "*"
  End if
End Sub

Thanks & regards,
Don

 

[inddon]

I've been looking in my glass ball but I can't find LOVMst, I am not a fortune teller Please provide a example.

Hello Belleke,

I forgot to upload the sample workbook. Please find attached the workbook for your reference.

Many thanks & regards,
Don

 

[inddon]

I just recorded the macro and have the below code:

    ActiveSheet.ListObjects("Tbl_Student").Range.AutoFilter Field:=1, Criteria1 _
        :="=David"
    ActiveSheet.ListObjects("Tbl_Student").Range.AutoFilter Field:=2, Criteria1 _
        :="=Male"
    ActiveSheet.ListObjects("Tbl_Student").Range.AutoFilter Field:=3, Criteria1 _
        :="=22"

Is there a smarter way than recording the above macro?

Thanks & regards,
Don

 

[Belleke]

Does this help?

 

[inddon]

Does this help?

Hi Belleke,

Thank you so much for the solution . I could use this for one of my other reports, wonderful.

With my post's requirement I am looking for filtering it in one VBA code. The input criteria for the report would be 3 parameters "Name", "Gender" and "Age". Based on these 3 input values it should filter the table all at once.

Could that be done using VBA?

Thanks again & regards,
Donald

 

[Belleke]

This one?
See attached.
Note all tree fields must be filled in.

 

[inddon]

This one?
See attached.
Note all tree fields must be filled in.

Works perfect. Thank you