CODEWITHSUNDEEP
vbaexcel
Jerome
Jerome

Reputation: 17

Auto filter doesn't seem to apply

I've got a list of values, and am trying to apply an auto filter to it. All this set in a while loop. While the filter value seems right, I have to enter Excel click on the filter menu and click okay again.

Dim zaehlerHeights As Double

zaehlerHeights = 0.4
While zaehlerHeights <= 25
    Sheets("Rohdaten WG22").Select

        Columns("S:S").Select
        Range("Tabelle13[[#Headers],[HOEHE_VON]]").Activate
        ActiveSheet.ListObjects("Tabelle13").Range.AutoFilter Field:=19, Criteria1 _
        :="<" & zaehlerHeights, Operator:=xlAnd

        Columns("T:T").Select
        Range("Tabelle13[[#Headers],[HOEHE_BIS]]").Activate
        ActiveSheet.ListObjects("Tabelle13").Range.AutoFilter Field:=20, Criteria1 _
        :=">" & zaehlerHeights, Operator:=xlAnd
    ActiveSheet.AutoFilter.ApplyFilter

Not sure why this is so. Any ideas?

Edit: So, thanks to Mike and Peh, we narrowed down the problem. It seems that the decimal separator is the problem. I use the German version of Excel, while Mike, for whom his solution works, probably uses the English/American version. The separator for me is the comma, while for Mike it is the dot. VBA, though translating my dot to a comma, won't apply the filter properly. We tried replace() for the variable, but that doesn't work either. If I go through enough loops to reach a integer value (e.g. 1.0) the filters work just fine.

So, still no solution, but a better picture of the error. I assume, that if I would using the English version of Excel, the code would work just fine. For me this is more of a bug.

Thanks in advance Jerome

Upvotes: 1

Views: 5286

Answers (4)

alpenjoe77
alpenjoe77

Reputation: 15

in case you still need a solution for comma-based Excel versions (e.g. German):

Dim minDateString as String
minDateString = Replace(CStr(CDbl([minDate])), ",", ".")
Debug.Print minDateString

... where [minDate] refers to a named range, you can replace with any value you'd like to.

Basically, what it does:

1) Get correct value in integer form => CDbl([minDate])

2) Convert from integer to string => CStr ()

3) Replace comma with point => Replace

Afterwards, you can call it like that:

.AutoFilter Field:=11, Criteria1:=">=" & minDateString, Operator:=xlFilterValues

It should work! :)

Upvotes: 0

Jerome
Jerome

Reputation: 17

A bit of time has gone by but to close the question:

It was in fact not an error by code (all tough it might could've been prevented by better code), but rather a problem with how MS Excel handles the decimal separator in the German version.

Short term solution: take a detour by reading the values into an array.

I posted the problem to Microsoft, but can't find the post anymore. In the meantime I switched to an English client, so the problem for me is no longer relevant.

Thanks for all the help Jerome

Upvotes: 0

Mike
Mike

Reputation: 1938

Maybe you need to clear the filters first:

Dim zaehlerHeights As Double
zaehlerHeights = 0.4
Sheets("Rohdaten WG22").Select
ActiveSheet.Range("Tabelle13").AutoFilter Field:=19
ActiveSheet.Range("Tabelle13").AutoFilter Field:=20
While zaehlerHeights <= 25

    ActiveSheet.ListObjects("Tabelle13").Range.AutoFilter Field:=19, Criteria1 _
    :="<" & zaehlerHeights, Operator:=xlAnd

    ActiveSheet.ListObjects("Tabelle13").Range.AutoFilter Field:=20, Criteria1 _
    :=">" & zaehlerHeights, Operator:=xlAnd

I also removed some unneeded code. You don't need to select or activate what you are going to be filtering, that is in the filter line.

Edit

Try this code, I've made a couple of slight modifications

Dim zaehlerHeights As Double
zaehlerHeights = 0.4
Sheets("Rohdaten WG22").Select
ActiveSheet.Range("Tabelle13").AutoFilter Field:=19
ActiveSheet.Range("Tabelle13").AutoFilter Field:=20
While zaehlerHeights <= 25

    ActiveSheet.Range("Tabelle13").AutoFilter Field:=19, Criteria1 _
    :="<" & zaehlerHeights, Operator:=xlAnd

    ActiveSheet.Range("Tabelle13").AutoFilter Field:=20, Criteria1 _
    :=">" & zaehlerHeights, Operator:=xlAnd

Upvotes: 0

Pᴇʜ
Pᴇʜ

Reputation: 57743

In your code is much that you don't need. You won't need all the .Select the field number in your filter already says which column to filter Field:=19.

Dim zaehlerHeights As Double

zaehlerHeights = 0.4
While zaehlerHeights <= 25
    With Worksheets("Rohdaten WG22")
        .ListObjects("Tabelle13").Range.AutoFilter Field:=19, Criteria1 _
        :="<" & zaehlerHeights, Operator:=xlAnd

        .ListObjects("Tabelle13").Range.AutoFilter Field:=20, Criteria1 _
        :=">" & zaehlerHeights, Operator:=xlAnd
    End With

or even shorter

    With Worksheets("Rohdaten WG22").ListObjects("Tabelle13").Range
        .AutoFilter Field:=19, Criteria1:="<" & zaehlerHeights, Operator:=xlAnd
        .AutoFilter Field:=20, Criteria1:=">" & zaehlerHeights, Operator:=xlAnd
    End With

So this should already filter, and apply the filter immediately.

Note
On non-English Excel versions where , comma is the digit separator you might run into problems when using a = like "=" & zaehlerHeights because zaehlerHeights = 0.4 is a dot (which cannot be changed) but Excel might need a , in the filter criteria so you might need to replace that . by a , (e.g. with the replace() function).

But the odd thing is when using a > or < or <= or >= then Excel awaits a .

Example:
eg. On a German Excel where the values are eg. 0,3; 0,4; 0,5 (note the comma) in Field19

'these work …
.AutoFilter Field:=19, Criteria1:="<0.4"
.AutoFilter Field:=19, Criteria1:="<=0.4"
.AutoFilter Field:=19, Criteria1:=">0.4"
.AutoFilter Field:=19, Criteria1:=">=0.4"

'this doesn't work …
.AutoFilter Field:=19, Criteria1:="=0.4"

'but this works …
.AutoFilter Field:=19, Criteria1:="=0,4"

So this might be why it doesn't work with VBA but it works later when you hit the apply filter manually.

Upvotes: 1

Related Questions