Using an if statement to filter

speric07

New Member
Joined
Sep 13, 2006
Messages
10
Hello everyone, sorry if this is in the wrong section please tell me if so.

Right, what I am trying to do is create a button where when I click on it it will automatically filter the data accordingly.

I have managed to get the each section of the if statement to work independently from one another. But it just does not seem to work combined.

Please find code attached code that works on it's own is highlighted in red thanks

If regionselect = "All" Then

Sheets("Raw Data").Select
Range("A1").Select
ActiveWindow.SmallScroll ToRight:=11
Selection.AutoFilter Field:=17
Sheets("Sheet1").Select
Else

Sheets("Raw Data").Select
Range("A1").Select
ActiveWindow.SmallScroll ToRight:=11
Selection.AutoFilter Field:=17, Criteria1:=Range("Regionselect")
Sheets("Sheet1").SelectEnd If


Thanks in advance..

Richard
 

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.

Harvey

Well-known Member
Joined
Nov 18, 2004
Messages
953
it looks like when the filter in the else part is set, and the first part is executed again, you will get the same filter. try using Selection.AutoFilter Field:=17, Criteria1:= "" or Criteria1:=nothing something like that should work i think.
 

speric07

New Member
Joined
Sep 13, 2006
Messages
10
Harvey,

I have tried doing this but am unsure I understand your workaround.

The first part of the if statement does not work.

The second part does. But using both part independently will also work.

Sorry for being a pain.

This statement is really getting on my nerves....
 

Harvey

Well-known Member
Joined
Nov 18, 2004
Messages
953
I a not really into this filters, but what I think what goes wrong is this:

as you see, the first part of your filter specifies no criteria.
now the first time this code will run, nothing strange happens.
However, when you run the second part, a criterium is set.
No when the first part is run again, nothing is done to overwrite that criterium or set it to nothing. Therefore, it will give the same result as the second part. If you fill in the criteria := .. part your problem is probably solved.
 

Von Pookie

MrExcel MVP
Joined
Feb 17, 2002
Messages
13,686

ADVERTISEMENT

I haven't completely tested this, but are you looking for something more along these lines?

Code:
If Range("Regionselect").Value = "All" Then
    Sheets("Raw Data").Range("A1").AutoFilter Field:=17
Else
    Sheets("Raw Data").Range("A1").AutoFilter Field:=17, Criteria1:=Range("Regionselect").Value
End If
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,300
Office Version
  1. 365
Platform
  1. Windows
Richard

If RegionSelect is a named range then the If won't work.
Code:
IIf Range("RegionSelect") = "All" Then
    Sheets("Raw Data").Range("A1").AutoFilter Field:=17
Else
    Sheets("Raw Data").Range("A1").AutoFilter Field:=17, Criteria1:=Range("Regionselect")
End If
 

speric07

New Member
Joined
Sep 13, 2006
Messages
10
Thanks all..................... i'm bit rusty in Excel VBA, usually just use Access.

I'm an idiot, can't believe I forgot the Range thing.

Many thanks though
 

Forum statistics

Threads
1,136,345
Messages
5,675,223
Members
419,553
Latest member
hanahass

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top