Link to home
Start Free TrialLog in
Avatar of Justin Smith
Justin SmithFlag for United Kingdom of Great Britain and Northern Ireland

asked on

problem with Autofilter macro

problem with Excel Autofilter macro. I have recorded a macro where I filter on row C and select from column 45 in a drop down filter the option ">1", then copy and paste the block of data tyanother tab. However, when I run macro, it selects all different fields (9M-1Y, 6-9M,) anyone any idea why?
Avatar of Rob Henson
Rob Henson
Flag of United Kingdom of Great Britain and Northern Ireland image

A copy of the file in question would be useful. Don't fully understand what you are describing; seem to have rows and columns mixed up.
Avatar of Justin Smith

ASKER

hi Rob, I cannot send it due to company firewalls. it's just a block of data that goes from C3 to BZ200. i just need to filter on a certain Time period in column 45 and I am trying to select ">-1" which means less than 1 month. when I record it, it works but when I run the macro it select other time periods in the column "3-6 months", "9-12 months". why would this happen?
Sub WAL ()        

Sheets ("CD"). Select
Rows ("3:3"). Select
Activesheet.Range ("A3:bz200). AutoFilter:=45, Criteria1:="<1M"
Range ("Y3").  Select
Think you might need to double up on the double quotes to make a text string:
Criteria1:=""<1M""
I am getting an error message when I add the double quotations - "Expected End of Statement"
ok, I tried Activesheet.Range ("A3:BZ200). Autofilter Field:=45, Criteria1:="""<1M""". but it does not select any option
What are the exact text values in the column needing filter? I will put together a file with some duff data
SOLUTION
Avatar of Rob Henson
Rob Henson
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Rob's knowledge of VB is superb