PDA

View Full Version : Advanced Filter



satyen
09-08-2009, 12:50 PM
Hello,
Hopefully someone can help as I have tried everything!

Defined Criteria
Fruit
Apple >60
Orange >40
Pear >200
* >30


I have tried to record a macro to do an advanced filter to extract everything over 20 for Apple and everything over 40 for Orange, which worked, but I would like the rest of the fruits it picks out to be >50.

Now, even if there is an Apple more than 34 for example it should not be included, because it should pick only over 60 as defined above. The number of defined fruits can be changed by the user in the spreadsheet and so can the default.


The source data has >1900 rows


Sub Macro1()
'

'

'
Sheets("Sheet2").Range("A1:B14").AdvancedFilter Action:=xlFilterCopy, _
CriteriaRange:=Range("A1:f5"), CopyToRange:=Range("i1"), Unique:=False
End Sub


thanks in advance.

Bob Phillips
09-08-2009, 02:00 PM
Can you post the workbook, it will help a lot.

satyen
09-08-2009, 02:32 PM
Please see attached. I have put raw data in "Source" sheet and output (starting in G1) that I would like in Sheet 1 along with criteria starting in A2.

Many Thanks

Bob Phillips
09-08-2009, 03:02 PM
They are all passing on the * test, it is not all but >35, it is all > 35. It would take a lot of trickery and code to avoid that I think.