Filter detection

1) You wrote: "It works great for heating but only that one.";
if it returns Criteria1 "=HEATING", there is no reason for it not to return "=SANITARY" or "=ELECTRICITY".

But maybe your first question wasn't for the same column, but for
the others? In that case, you just need to put the correct filter number, that is to say
the number of the corresponding column. So:

a) For column 1 "Member", it's filter 1
b) For column 2 "Activity", it's filter 2
c) For column 3 "Supplier", it's filter 3
d) For column 4 "Selling price", it's filter 4
e) For column 5 "Cost price", it's filter 5

So you just need to put the correct number n for:
Worksheets("Sheet1").AutoFilter.Filters(n).Criteria1

But leave Criteria1! Criteria2 is for a test that includes 2 values.

Example: is greater than or equal to 20 And is less than or equal to 100.
In this case, Criteria1: ">=20" and Criteria2: "<=100".

-------------------------------------------------------

2) Look at this line of code:

MsgBox Worksheets("Sheet1").AutoFilter.Filters(n).On
n is the column number of the table = filter number
On is for On / Off: active or not

If there is no filter criterion for the column in question,
it returns FALSE; otherwise, it returns TRUE,
and only then check the filter criteria.

For what you're asking, I believe there is no other way
than to compare, for each filter, the values "before" and "after"
first of On, then possibly of Criteria1 and Criteria2.

Because if On was before FALSE then became after TRUE (or vice versa),
that's enough to determine that the filter for the column in question has been
modified: no need then to test Criteria1 (and even less Criteria2).

What I mean is that you should test Criteria2 only if, for before
and after: On = TRUE and Criteria1 is the same.

And you should also check .Operator: 1 for And; 2 for Or

-------------------------------------------------------

3) When we use an automatic filter, it is fortunate that the rows that
do not match the criterion(s) are only hidden and not
deleted! This is completely normal, and it also allows to:

a) Change the criterion, and see again the rows previously hidden
that correspond to the new criterion(na).

b) Disable the automatic filter, and see again all the rows
of the table.

But if your line of code:

LastLineSheet1 = Worksheets(1).Range("C"&Rows.Count).End(xlUp).Row

does not return the number of the last row, the only explanation I see is that you have one or more other data under your
table, and thus the .End(xlUp).Row gets stuck on it, before being able to "encounter" the last row of your table located higher.

Looking forward to your reply. ????

Thank you very much, it's really helping me make progress.
So, to count the unhidden lines I've done this:

LastLineFeuil1 = Worksheets(1).Range("D" & Rows.Count).End(xlUp).Row
For l = 4 To LastLineFeuil1
 If Not Rows(l).Hidden Then R = R + 1
Next l

Probably not the best solution but it does what it needs to do.

Then I have a rather tricky problem:

If (Worksheets(1).AutoFilter.Filters(4).On = True) Then
 MsgBox Worksheets(1).AutoFilter.Filters(4).Criteria1
 MsgBox (R)
Else
 MsgBox ("Supplier filter not used")
End If

I just shifted the test column but I get an error that occurs sometimes, I can't identify the source. Error '91' Object variable or With block variable not set.

If I understand what it means, shouldn't it go into the else of my loop?
Then, I didn't quite understand how to test the filter change.
Should I store the current filter value in a string and then compare it with the next one?

The user will definitely have to use a button to launch the test, right?

See you soon.

Hello Shadorb,

1) To count the unhidden rows, try this instead:

 LastLineFeuil1 = Worksheets(1).Range("D" & Rows.Count).End(xlUp).Row R = WorksheetFunction.Subtotal(103, Range("D4:D" & LastLineFeuil1)) 

- no need for the For l.. Next l loop (l lowercase, not I uppercase)
- no need for the If Not Rows(l).Hidden Then R = R + 1 test

But be careful to always leave 103: that's the code for COUNTA
so that the Subtotal function returns the number of values
in the specified range while ignoring hidden rows.

---------------------------------------------------------

I used the same starting line as you: 4; assuming your header row is in row 3, and the first data row is row 4, just
below.

I also kept your same column D, but you can choose another one
if you prefer. In any case, the result will be the same since for
all the columns of your table, the hidden rows are of course
the same.

---------------------------------------------------------

For more information on the function used, type in a blank cell
of your Excel sheet: =subtotal( ; click on fx in front of the formula bar,
and check the online help. Don’t forget the point (.) between “sub” and “total”!

=================================

2) In your Else, there is: "Supplier filter not used", which therefore indicates that
you are testing the filter of the "Supplier" column. However, on the screenshot of your
initial table, it is column number 3; so your filter is number 3, not 4!

Unless, of course, you have added another column before, but I don't think so; if you have changed the structure of your table, send me another screenshot (with row numbers and column letters)!

---------------------------------------------------------

You have put unnecessary parentheses, which makes me think that you have already
programmed in Turbo-Pascal before, right?  😉

The " = True" test is also unnecessary; the 2 successive MsgBoxes can be combined
into one (advantage: 1 box instead of 2); and by “factoring” filter 3, it gives:

 With Worksheets(1).AutoFilter.Filters(3) If .On Then MsgBox .Criteria1 & vbLf & R Else MsgBox "Supplier filter not used" End If End With 

On the other hand, note that your 2 previous lines to calculate R
are only useful if we display this R; so since it's useful for the first part of the If only, and not for the Else, we move the
2 lines in question into the If, before using R:

 With Worksheets(1).AutoFilter.Filters(3) If .On Then LastLineFeuil1 = Worksheets(1).Range("D" & Rows.Count).End(xlUp).Row R = WorksheetFunction.Subtotal(103, Range("D4:D" & LastLineFeuil1)) MsgBox "Criterion: " & .Criteria1 & vbLf & "Number of rows: " & R Else MsgBox "Supplier filter not used" End If End With 

---------------------------------------------------------
Attention!

Do not move your first line if the rest of your code needs LastLineFeuil1;
do not move your second line if the rest of your code needs R.
---------------------------------------------------------

You will also notice that I added a little text before displaying the criterion
and before R; if you like it less, put another text or remove "“...“ & ".

=================================

3) Error 91 does not concern whether to enter the Else or not; if it's
"Object variable not defined", it means that VBA cannot find an object
that something (expression, method, property, or else) depends on;
if it's "Block variable not defined", the block is not necessarily an If..Then..Else block, it can also be a With..End block or something else...

I don't think your error 91 was due to your filter number error,
but check if it reproduces when you set filter number 3 (with
my code above). Because if that has resolved, there's no need to look further!

However, if it does reproduce, I would need you to show me the part of code
where it occurs, and also tell me on which line it stops and highlight in yellow
when you click on the debug button.

=================================

4) Setting up a button for the user to run the macro that checks whether there has been a filter change or not depends on what you want to do; and for what purpose would you want the user to know that the filter has been modified?

So it is not necessary for the user to run this test themselves:
if you really need it, you can do it automatically via VBA,
without any user intervention; knowing at what time you want this test to be made: for example, only when running the corresponding macro, or based on an event like "on activating
the Worksheets(1) sheet" or something else...

=================================

This present message is already super long, so for the way to test
the filter change, we will see that another time, when you have resolved
all the problems above.

Best regards.  😊

When you explain, you really go all out, it's cool!

Indeed, the table has slightly changed, and with the filter below activated, it crashes.
http://hpics.li/59c9dff

Since I don't know how to highlight in yellow on the site, I'm sending you a debugging image again.
http://hpics.li/dbc6d44

Honestly, I don't understand this error. I have activated filter number 4. I must not understand something about how the filters work.