filtering multiple values via search box

Question

When using auto filter, i know that i can have a filter with multiple values. if the list is long i can use the search box pick my selection and select "add current selection to filter".

Today, i lookup each value individually and select "add current sel....". Is there a syntax I can use to submit all my values in the search box at one time, then pick what i need and hit ok? even if there's a limit, it's still better than one by one.

so if i have a list of 50 abbrev states, and i want to pick 5, can I enter NY, NJ, PA, CT, FL in the search box and have them all come up for me to select at once rather than look up each state individually and append. I tried using "," ";" and " " but none worked.

And my values are different, i'm just using states to simplify my question. I did try on a list of states and it didn't work just in case there was an issue with my alphanum values.

And i know i can use "contains" text filters but this takes just as long. this is not a permanent condition, these are adhoc searches i do on this master reference file all the time so the context always changes.

thanks

hile

Answer 1

Hi,

try this sample, using vba macro

method1

multiple criteria, in cell A1 (comma separated)

data in range C1:C18

vba macro

Sub AutoF_MultipleCriteria_InOneCell()

'Aug 31, 2016

Dim ws As Worksheet

Set ws = ActiveSheet

ws.AutoFilterMode = False

Dim rng As Range

v = Split(ws.Range("A1").Value, ",")

Set rng = ws.[C1:C18]

rng.AutoFilter Field:=1, Criteria1:=v, Operator:=xlFilterValues

MsgBox "next"

ws.AutoFilterMode = False

End Sub

XXXXXXXXXXXXXXXXXXXXX

also,

method2

criteria in range A1:A4

vba macro

Sub AutoF_MultipleCriteria_Array()

'Aug 31, 2016

Dim ws As Worksheet

Set ws = ActiveSheet '<< data in active sht

Dim v As Variant

Dim t As Long, r As Long, x As Long

Dim rng As Range

t = ws.Cells(Rows.Count, "A").End(xlUp).Row ' << number of criteria in col A

r = ws.Cells(Rows.Count, "C").End(xlUp).Row ' << number of data in col C

ws.AutoFilterMode = False

ReDim v(1 To t)

For x = 1 To t

v(x) = ws.Cells(x, 1)

Next

Set rng = ws.Range("C1").Resize(r)

rng.AutoFilter Field:=1, Criteria1:=v, Operator:=xlFilterValues

MsgBox "next"

ws.AutoFilterMode = False

End Sub

Answer 2

You may use Advanced filter also for this which I believe is more convenient. 

My data is in column C while my criteria is in column A.

See this snip shot.

This is result after applying OK.

Answer 3

these methods did not work. i think it is because of the values in my cells, i have alphanumeric values and indents. so a cell may have 0666-Location. when i entered '0666 in my data column it didn't work, 666 didn't work either, even 7850 didn't work. when i type in the numbers in the auto filter box it does work, so i'll just keep entering them individually and appending.

It's not impt. enough to spend an inordinate amount of time on it. so i truly thank you for your prompt responses. i was trying to see if there was a quick fix.