Using Data Filter With Macro

[Richard]

I have a spreadsheet with one column containing the name
of companies. I wished to create a drop down box, which
would, when an option was picked, automatically filter the
column (and the other data in the table) just to the
entries for relevant company selected. I have now found
that you cannot set the filter to reference a cell, which
kind of stops me in my tracks.

To try get the whole thing working i had set up a vlookup
column next to the text column (of companies), to convert
the companies to a number, which corresponded with the
drop dorwn box cell link.

If anyone can help me filter data from the result of a
drop down box please let me know.

Thanks

Richard

 

[Bernie Deitrick]

Richard,

Using AutoFilter creates a dropdown at the top of the relevant column
that does exactly what you describe.

However, if you want to link your filter to a cell's value, then
you'll need to use the worksheet's change event to do it. For
example, if you want to filter A1:B50 based on column A, with the
filtered value being entered in cell D1, then you could use this:

Private Sub Worksheet_Change(ByVal Target As Range)
Range("A1:B50").AutoFilter Field:=1, Criteria1:=Range("D1").Value
End Sub

Copy the code, right click on the sheet tab, select "View Code" and
paste the code in the window that appears.

HTH,
Bernie
MS Excel MVP