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

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number

VoG

Legend
Joined
Jun 19, 2002
Messages
63,650
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
 

nicolewh

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

Legend
Joined
Jun 19, 2002
Messages
63,650
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
 

VoG

Legend
Joined
Jun 19, 2002
Messages
63,650

ADVERTISEMENT

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
 

nicolewh

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

VoG

Legend
Joined
Jun 19, 2002
Messages
63,650

ADVERTISEMENT

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.
 

nicolewh

Well-known Member
Joined
Aug 7, 2009
Messages
554
Any way to place a range within the array intead of individual variables? I have this new thread I started here

Thanks
 

Watch MrExcel Video

Forum statistics

Threads
1,129,553
Messages
5,636,989
Members
416,953
Latest member
broexc

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
Top