Excel 2003 VBA code for filtering

Closed
Ron - Mar 25, 2009 at 01:20 AM
 anonymous pseudonym - Jan 17, 2010 at 09:16 PM
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?

1 response

bloodveyors Posts 4 Registration date Saturday March 21, 2009 Status Member Last seen June 16, 2009 1
Mar 25, 2009 at 07:37 AM
hi,
try this link to see if it helps you:
http://www.ehow.com/how_4501336_use-auto-filter-microsoft-excel.html
0
anonymous pseudonym
Jan 17, 2010 at 09:16 PM
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.
0