Data Filter linking to cells

[Swaroon]

Hi,

I have a file containing 500+ rows of data, column H lists the programme agreement reference per row, out of the 500 rows, there might be 25 different agreement codes.

Can I enter a prog agreement number in a cell, and get the data filter funtion to reference the cell, therefore if I enter agreement AAA in cell A1, the macro will filter the 500 rows showing only those rows where the agreement is AAA ?

Ideally, I would like to be able to filter on up to 5 agreement numbers, these could be listed in cells A1:A5 ?

Any ideas........I haven't !
thanks
Steve

 

[Sahak]

Hi Swaroon:

Try this:
It will work with double click
Suppose your data located in D10:F500 & your agreements are in A1:A5 (it can be in any range)

Option Explicit
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    Dim FiltVal As String
    FiltVal = ActiveCell.Value
    Worksheets("Sheet1").AutoFilterMode = False
    Range("D10:F500").AutoFilter
    Selection.AutoFilter Field:=1, Criteria1:=FiltVal
End Sub

 

[Swaroon]

Hi Sahak,

Works great, thanks for your help, appreciate it.

cheers
Steve

 

[Sahak]

You are welcome.