Macro for table to include only certain values within multiple columns

nicolewh

Well-known Member
Joined
Aug 7, 2009
Messages
554
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
 

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.
Try

Code:
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
 
Upvote 0
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!
 
Upvote 0
Try this

Code:
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
 
Upvote 0
Also, is there a way to use multiple criteria for each column.

Example with alternatives for column C

Code:
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
 
Upvote 0
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!

Code:
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
 
Upvote 0
Hi Nicole

Suggested changes

Code:
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

Code:
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.
 
Upvote 0
Any way to place a range within the array intead of individual variables? I have this new thread I started here

Thanks
 
Upvote 0

Forum statistics

Threads
1,216,095
Messages
6,128,795
Members
449,468
Latest member
AGreen17

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top