Using Data Filter With Macro

  • Thread starter Thread starter Richard
  • Start date Start date
R

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
 
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
 
Back
Top