CODEWITHSUNDEEP
vb.netexcelvba
AlexS
AlexS

Reputation: 520

Excel Advanced Filters - Filter a table with multiple cell values

I've been grappling with this for most of the day and managed to crash Excel 2010 a few times in the process with borrowed code from the web.

I have a table (see below) with 1 column of unique values called 'ID'. I also have a list of ID's e.g. [2 5 7 8] that are a subset of those in the table. I'd like to only show the table entries that correspond to those in the subset.

I'm sure it must be possible to do but I have limited VB skills.

ID   A__    B ____     C _
1,   1.1,   2.1,      -2.1
2,   1.2,   2.2,      -2.1
3,   1.3,   2.3,      -2.1
4,   1.4,   2.4,      -2.1
5,   1.5,   2.5,      -2.1
6,   1.6,   2.6,      -2.1
7,   1.7,   2.7,      -2.1
8,   1.8,   2.8,      -2.1

Could someone suggest a solution for this?

Thanks Alex

Upvotes: 0

Views: 1313

Answers (1)

Peter Albert
Peter Albert

Reputation: 17475

There's no need to do this in VBA.

Simply add another column and populate it with this formula: (assumption is that it starts in row 2, the ID is in column A and that your list of selected IDs is in Sheet2!A1:A10 - adjust accordingly)

=--NOT(ISERROR(MATCH(A2,Sheet2!$A$1:$A$10,0)))

Copy this formula down. This formula will return TRUE for every line that has an ID in the list - and FALSE else. Now you only need to apply an AutoFilter and filter for TRUE - done! :-)

If you place this code in the worksheet with your selection, the autofilter will update automatically:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Me.Range("A1:A10")) Is Nothing Then Exit Sub

    With Worksheets("Sheet1")
        .AutoFilterMode = False
        On Error Resume Next
        .Range("$A:$C").AutoFilter Field:=3, Criteria1:="1"
    End With
End Sub

Upvotes: 1

Related Questions