Auto filter doesn't seem to apply

Question

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

As you can see I already tried autoFilter.ApplyFilter, which doesn't give me anything. — Jerome, Feb 21 '18 at 13:32
"*Not sure why this is so.*" Why is what? What would you expect instead? — Pᴇʜ, Feb 21 '18 at 13:35
@PehI would expect the filter to apply itself. Instead I am left with a list of zero results, but simply (manually) reactivating the filter fixes the problem. — Jerome, Feb 21 '18 at 13:37
OK, thx for clarification, but wouldn't the next loop kick out the filter criteria of the previous loop? So only the last loop criteria would apply to the data at all? And I recommend to [avoid `.Select` and `.Activate`](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba) when ever possible. — Pᴇʜ, Feb 21 '18 at 13:42
Yes, that's because the code block is missing it's second (not yet active) half, where I take the values and copy them to another table. But since I didn't get the first part right I have nothing to copy. :-) Will do, cheers — Jerome, Feb 21 '18 at 13:55
I do have another question. What does happen if we assume on start all entries are visible: If you run your code do the entries remain visible (just not being filtered at all) or does something other happen? — Pᴇʜ, Feb 21 '18 at 17:28
Good morning, Peh. Nope, Filters apply, but no entries are found. (But entries are found if filtered manually) — Jerome, Feb 22 '18 at 08:50
**Ahhh, now that turns out to be something complete different now!** Your title is "*Auto filter doesn't seem to apply*" but that's just wrong, it **does** apply, but it does not filter correctly! So this is not a "filter does not apply" error it is a "filter applies but does not filter correctly". If you ask the wrong question you will obviously get the wrong answers. You **must** be very specific if you ask here. This make a huge difference. So it is definitely an issue with your criteria, probably something with the `.` and `,` or text/value based filter. — Pᴇʜ, Feb 22 '18 at 11:01
How are your columns (HOEHE_VON and HOEHE_BIS) formatted? Are they formatted as (Default = Standard) or are they formatted as text or any specific number format applied? Can you maybe provide a screenshot of these columns? — Pᴇʜ, Feb 22 '18 at 11:04
Well, it does not apply in terms of the result is not correct. I've made a screen recording, but didn't know where to upload it. But since it seems to be about the decimal separator I don't think the code is broken, but rather the method how VBA handles the separator in the German version. I gonna take a detour by reading the values into an array (which works fine) and get the discussion about the separator to the support guys from Microsoft. Thanks for your help, and sorry for the misunderstanding — Jerome, Feb 22 '18 at 12:51

Pᴇʜ · Answer 1 · 2018-02-21T14:41:32.573

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.

I guess why "=0.4" doesn't work is that Excel changes into a text based filter. Anyhow, still no results. This drives me mad, in the end it's gonna be so obvious. Thanks for your help tho! — Jerome, Feb 21 '18 at 15:53
@Jerome yes I assume the same, that it changes into a text based filter, but this sucks because even a filter like `"=0,40"` doesn't work on a value that is `0,4`. The value is the same but the text filter makes it useless in VBA. On the other hand manual filtering for `0,40` works. This makes filtering values a real pain. An idea (just in case you didn't check) make sure the columns you try to filter are formatted as numbers not as text. — Pᴇʜ, Feb 21 '18 at 16:39

Mike · Answer 2 · 2018-02-21T16:04:54.233

0

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

edited Feb 21 '18 at 16:04

answered Feb 21 '18 at 13:43

Mike

1,853
3
45
75

Thanks for the fast response, Mike. Unfortunately this does not work either. I still have a list with zero results. Do you think I need to define that I wanna use a numeric filter? – Jerome Feb 21 '18 at 13:54
@Jerome I've made some slight modifications to the code, please try the second iteration. – Mike Feb 21 '18 at 14:06
Nope, still nothing. Thank you anyway. – Jerome Feb 21 '18 at 15:55
@Jerome Are you stepping through the code so that you see each time it is setting the auto-filter or just hitting run? These both work fine for me. – Mike Feb 21 '18 at 16:06
Single steps, I gonny try to take a short video-recording later – Jerome Feb 21 '18 at 16:39

score 0 · Accepted Answer · answered Oct 26 '18 at 08:03

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

score 0 · Answer 4 · answered Nov 03 '18 at 00:17

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! :)

Hey @alpenjoe77 thank you for your input, unfortunately the script itself doesn't exist in this form anymore so I can't evaluate your suggestion, but since we tried different replace approaches I am not sure about it. All the best Jerome :-) — Jerome, Nov 05 '18 at 07:07

Auto filter doesn't seem to apply

4 Answers4