Filter Macro (range contained in filter value)

**antman10** · 11-27-2010, 08:03 AM

Hi

Does anyone know a macro that filter as follows:

Sheet1: Table: Name

1 Name
2 Fred
3 Bill
4 Jane

Sheet2: Active Cell: "Fred, Bill"

I want to filter the Name table in Sheet A based on those names that are contained in the current active cell in Sheet 2 (list of names comma separated). In this case I would expect the table to show only row 2 and 3.

Any help appreciated!

Cheers

**royUK** · 11-27-2010, 09:03 AM

To best describe or illustrate your problem you would be better off attaching a dummy workbook, the workbook should contain the same structure and some dummy data of the same type as the type you have in your real workbook - so, if a cell contains numbers & letters in this format abc-123 then that should be reflected in the dummy workbook.

If needed supply a before and after sheet in the workbook so the person helping you can see what you are trying to achieve.

Doing this will ensure you get the result you need!

**davesexcel** · 11-27-2010, 09:35 AM

Here is one way

Please Login or Register  to view this content.

You can only filter 1 column with two criteria in XL'03.

**antman10** · 11-28-2010, 04:33 AM

Hi

Thanks for the reply. The only issue with this solution is that the active cell could contain one or more values separated by commas, so it would need to allow for a variable number of comma separated values.

Refer to the attached spreadsheet. What I want to be able to do is select a cell in Worksheet 2 (one of the groups of names), and then run the macro (through a shortcut key sequence). The macro is to then activate worksheet1 and filter the names table to only show those in the active cell on worksheet 2. Typically, the table in worksheet would have many columns such as age, address, employer etc.

It would be good if I could just issue a filter to state "where the "*" + range value +"*" = search value. I know you can do the reverse.

Cheers

**antman10** · 11-28-2010, 04:35 AM

BTW, happy to use Excel 2007 if it helps!

**Maistrye** · 11-28-2010, 05:14 AM

Hi,

I've attached a sample of a possible non-macro way to do this. I'm not sure how much you need explained for this, hopefully there's enough. :-)

The Details tab contains the list of things that was done to make it functional, plus things to keep in mind to extend it.

Essentially this solution makes liberal use of Named Ranges.

The only manual portion of this is you would need to filter in Column A for "Yes" in order to show just those you want to see.

S

**davesexcel** · 11-28-2010, 10:17 AM

Originally Posted by antman10

BTW, happy to use Excel 2007 if it helps!

What you use should be in your profile, you have 2003.

Try this,

Please Login or Register  to view this content.

Select what to find in Sheet2, then loop through column1 sheet1 and if search item is not found it will hide the row.

*If your are using XL'07, can't you just go to sheet1 and filter what you want?

**antman10** · 11-28-2010, 07:31 PM

Thanks Dave - I will take a look at this.

Yes I could always go to Sheet 1 and do this manually however I want to do it automatically by selecting a comma separated cell. There is a reason for this however didnt want to waste your time with the whole businss context.

Cheers.

**antman10** · 11-28-2010, 08:35 PM

Dave

Your solution works fine however I really want to use filtering rather than hiding rows (as this is the user interface I require in thsi case).

An idea I have is to try and parse the CSV cell into an array, and then to use the following:

ActiveSheet.ListObjects("Table1").Range.AutoFilter Field:=1, Criteria1:= _
Array("bill","jane","fred"), Operator:=xlFilterValues

I now just need to find an exampel of parsing a CSV into an array object.

Cheers

**antman10** · 11-28-2010, 11:09 PM

Think I have worked it out. This seems to work:

Sub Macro2()

Dim vArray()
Dim vCnt As Long
Dim vInput, vLeft As String

vInput = ActiveCell.Value

vInput = LTrim(RTrim(vInput)) & ","

vCnt = -1

While InStr(vInput, ",")
vLeft = Left(vInput, InStr(vInput, ",") - 1)
vInput = Right(vInput, Len(vInput) - InStr(vInput, ","))
If (vLeft <> "") Then
vCnt = vCnt + 1
ReDim Preserve vArray(vCnt)
vArray(vCnt) = vLeft
End If
Wend
If (vCnt >= 0) Then
ActiveSheet.ListObjects("Table1").Range.AutoFilter Field:=1, Criteria1:= _
Array(vArray), Operator:=xlFilterValues
Else
MsgBox "No data to filter"
End If
End Sub