Using an if statement to filter

[speric07]

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

 

[Harvey]

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]

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]

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]

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

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]

Richard

If RegionSelect is a named range then the If won't work.

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]

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