Excel 2003 VBA code for filtering

Closed
Ron - 25 Mar 2009 à 01:20
 anonymous pseudonym - 17 Jan 2010 à 21:16
Hello,

I need to perform an Autofilter function in Excel 2003 that allows me to filter a list using the contents of another cell as the criteria. For example, I need to filter the data in Column I against the value in cell E6. Now I know the basic code to filter against values equal to the contents of E6, which is:

Range("I11").Select
Selection.AutoFilter
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 3
Selection.AutoFilter Field:=9, Criteria1:=Range("E6"), Operator:=xlAnd

However, how do I filter against values less than or equal to E6? I've tried playing around with this code but it only lets me set the criteria as less than or equal to a numeric value, i.e.

Range("I11").Select
Selection.AutoFilter
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 3
Selection.AutoFilter Field:=9, Criteria1:="<=7", Operator:=xlAnd

How can I write a code so that the list in Column I is filtered against values that are less than or equal to the contents of cell E6?
Related:

1 response

bloodveyors Posts 4 Registration date Saturday 21 March 2009 Status Member Last seen 16 June 2009 1
25 Mar 2009 à 07:37
hi,
try this link to see if it helps you:
http://www.ehow.com/how_4501336_use-auto-filter-microsoft-excel.html
anonymous pseudonym
17 Jan 2010 à 21:16
Hi, I am not sure how that link will help as the question is in regards to VBA code, not basic excel use.

The real answer is to combine the ">=" operator with your source cell so that it reads like this:

Range("I11").Select
Selection.AutoFilter
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 3
Selection.AutoFilter Field:=9, Criteria1:=">=" & Range("E6")

Hope this helps.