Custom Autofilter Based on Cell Reference Value

Hi all,

I am trying to create a macro that filters out rows of data if Column 4 (PE Ratio) is more than cell reference "D2" (average industry PE Ratio). I also want to eliminate any rows where Column 4 (PE Ratio) reads "NA" - filtering out the "NA" rows is the easy part, but I am having trouble filtering out the rows that have PE Ratios (column 4) greater than the cell reference PE ratio ("D2").

Sorry if that's a mouthful. Here is the section of code I have now:

Range("A3:J3").Select 'Using Range instead of autofilter because the size of data varies when I import it.
Range(Selection, Selection.End(xlDown)).Select
Application.CutCopyMode = False
Selection.AutoFilter
Selection.AutoFilter Field:=4, Criteria1:="<" & Range("D2").Value 'THE PROBLEM
Selection.AutoFilter Field:=4, Criteria1:="<>*NA*", Operator:=xlAnd 'THIS LINE WORKS

Can anybody tell me how to tweak this code so that I can filter out rows based on my cell reference ("D2") value? D2 is a value, not a range, and it changes every time I click a button and import new data.

Thanks,

S Tassan

Maybe you could try this. It's working on the fake PE data sheet that I threw together.
I'm using your procedure, with the suggested modification, to add the autofilter.

Please Login or Register  to view this content.