CODEWITHSUNDEEP
excelfiltering
JBB
JBB

Reputation: 1

Excel 2010 - Filter Worksheet Based Upon Values In a Column In Another Worksheet

Worksheet 1 ("WS1") has many rows and 26 columns.

Worksheet 2 ("WS2") has fewer rows and 17 columns.

WS2|Column A contains a subset of data found in WS1|Column H.

These two columns have the same labels in the Header row.

I want WS1 to only display rows whose Column H values are found somewhere in W2|Column A.

I can do this manually, of course:

  1. Set filters on the header row of WS1
  2. Expand the filter for WS1|Column H
  3. Deselect all
  4. Click the box beside each value that is found in WS2|Column A

But that is too cumbersome. I've tried to figure out way to do this with AutoFilters but that doesn't seem possible.

What is the best way to accomplish this?

Note that the values in WS2|Column A may change over time. It would be awesome if anytime I do this it will be based upon the current values in WS2.

Upvotes: 0

Views: 1318

Answers (1)

Gary's Student
Gary's Student

Reputation: 96753

Try this small macro:

Sub AutoFilterHelper()
    Dim sh1 As Worksheet, sh2 As Worksheet, N As Long
    Dim ary()
    Set sh1 = Sheets("WS1")
    Set sh2 = Sheets("WS2")

    N = sh2.Cells(Rows.Count, "A").End(xlUp).Row
    ReDim ary(1 To N - 1)

    For i = 1 To N - 1
        ary(i) = sh2.Cells(i + 1, "A").Value
    Next i

    sh1.Range("H:H").AutoFilter Field:=1, Criteria1:=(ary), Operator:=xlFilterValues
End Sub

It will grab the data from WS2 and use it for the filter criteria in WS1.

If WS2 changes, just re-run the macro.

Macros are very easy to install and use:

  1. ALT-F11 brings up the VBE window
  2. ALT-I ALT-M opens a fresh module
  3. paste the stuff in and close the VBE window

If you save the workbook, the macro will be saved with it. If you are using a version of Excel later then 2003, you must save the file as .xlsm rather than .xlsx

To remove the macro:

  1. bring up the VBE window as above
  2. clear the code out
  3. close the VBE window

To use the macro from Excel:

  1. ALT-F8
  2. Select the macro
  3. Touch RUN

To learn more about macros in general, see:

http://www.mvps.org/dmcritchie/excel/getstarted.htm

and

http://msdn.microsoft.com/en-us/library/ee814735(v=office.14).aspx

Macros must be enabled for this to work!

Upvotes: 0

Related Questions