Macro for table to include only certain values within multiple columns

[nicolewh]

I know this may sound simple but I'm having the toughest time right now. I need a macro to only include certain values within multiple columns in a table. Let me explain through this example:
I have a table, table1
This is my criteria for column2:
Banana
This is my criteria for column3:
White

Column1 Column2 Column3
1.....Apple..... .......Orange
2 .....Grape ..... .....white
3 .....Banana .........Orange
4 .....Grape ...........Blue
5 .....PineApple...... Pink
6 .....Pear .............White

The result for table1 would be
Column1 Column2 Column3
2 .....Grape ..... .....white
3 .....Banana .........Orange
6 .....Pear .............White

As you can see the results include both criteria for both columns. How would I do this via macro?

Thank you!

Nicole

 

[VoG]

Try

Sub atest()
Dim LR As Long, i As Long
LR = Range("A" & Rows.Count).End(xlUp).Row
For i = LR To 1 Step -1
    If LCase(Range("B" & i).Value) <> "banana" And LCase(Range("C" & i).Value) <> "white" Then Rows(i).Delete
Next i
End Sub

 

[nicolewh]

I don't want to delete the other values (I think thats what you are doing with 'delete'). Just temporary hide them like your filtering.
Also, is there a way to use multiple criteria for each column. Thanks VoG!

 

[VoG]

Try this

Sub btest()
Dim LR As Long, i As Long
LR = Range("A" & Rows.Count).End(xlUp).Row
For i = LR To 1 Step -1
    Rows(i).Hidden = LCase(Range("B" & i).Value) <> "banana" And LCase(Range("C" & i).Value) <> "white"
Next i
End Sub

 

[VoG]

Also, is there a way to use multiple criteria for each column.

Example with alternatives for column C

Sub btest()
Dim LR As Long, i As Long
LR = Range("A" & Rows.Count).End(xlUp).Row
For i = LR To 1 Step -1
    Rows(i).Hidden = LCase(Range("B" & i).Value) <> "banana" And IsError(Application.Match(LCase(Range("C" & i).Value), Array("white", "pink"), 0))
Next i
End Sub

 

[nicolewh]

Thank you so much! After going through many trail and error moments I think this is it! I took your helpful code and contructed this. So far it works. If you can will you please give me any improvements you would add? Thanks!

Sub btest2()
Dim LR As Long, i As Long
Dim OC, SC, TC, FC   As String
Dim Wht, Pik, Pine, Taco As String
Wht = "white"
Pik = "pink"
Pine = "Pineapple"
Taco = "Pear"
LR = Application.WorksheetFunction.Subtotal(2, Range("Table1")) + 1
OC = xl_Col(Range("Table1[Column2]").Column)
SC = xl_Col(Range("Table1[Column3]").Column)
TC = xl_Col(Range("Table1[Column4]").Column)
FC = xl_Col(Range("Table1[Column5]").Column)

For i = LR To 2 Step -1
    Rows(i).Hidden = IsError(Application.Match(LCase(Range(SC & i).Value), Array(Wht, Pik), 0)) _
    And IsError(Application.Match(LCase(Range(OC & i).Value), Array(Pine, Taco), 0))
    
Next i
End Sub

btw- I can't beleive Excel would not have such a basic function that we would have to come up with this. :S

 

[VoG]

Hi Nicole

Suggested changes

Dim OC As String, SC As String, TC As String, FC   As String
Dim Wht As String, Pik As String, Pine As String, Taco As String
Wht = "white"
Pik = "pink"
Pine = "pineapple"
Taco = "pear"

With

Dim OC, SC, TC, FC   As String

you are declaring OC, SC and TC as Variant and FC as String. It won't matter in this case but in other cases it could matter.

You need to assign all of your values in lower case since the comparison later is against lower case strings.

Yes it does appear difficult and this type of multi-alternative test (using Match and an array) only came to me a short while ago whilst answering another question on the board. I've not seen this technique before. I think that an alternative, less convoluted, approach would be Advanced Filter.

 

[nicolewh]

Thanks for your reply and input. Have a sweet day.

 

[nicolewh]

Any way to place a range within the array intead of individual variables? I have this new thread I started here

Thanks