Excel filter does not working anymore

[Berry]

Can anyone help me with this problem:

I use a code in my excel sheet to hide rows when there is a "0" (zero)
or a "C" is in a cell in the colomn. Now when I use my auto filter,
it's not filtering anything!

This is how my sheet lookslike:

1 C
1 H
2 M
2 T
3 K
3 H
4 0

This is how the code lookslike:

Dim X As Boolean

Private Sub Worksheet_Calculate()
Dim R As Long
If X = True Then Exit Sub
X = True
For R = 1 To Cells(65535, 3).End(xlUp).Row
Select Case Cells(R, 3).Value
Case "", 0, "C"
Me.Rows(R).Hidden = True
Case Else
Me.Rows(R).Hidden = False
End Select
Next
X = False
End Sub

What I want is to use the auto filter on the rows, but if I do this
now, the filter does not filter anything!

Who can help with this problem?

Greets, Berry

 

[Dave Peterson]

When you posted this before, you were asked what X equals.

I don't recall seeing your response.

But if X is set to True, then your code simply exits the routine.

And is there a reason you're not using data|filter|autofilter (and dump the
worksheet_calculate event)???

 

[Berry]

Dave, the reason why I use the worksheet calculate event is because it
has to go automaticly!
I don't understand your question... What do I have to fill in by X??
I don't have so much experience with excel macro/code's.

This sheet is linked to another sheet, This sheet has to fill in
automaticly and is locked to change.
Do I have to fill in a new macro, or can I change something in it?

Thanx for your reply.
Greets

 

[Dave Peterson]

Your code does this:
If X = True Then Exit Sub

How does X get set?

If it's only in your code, then after the first calculate, it'll be true.
You have
X = True
in the next line.

Maybe this should not be automatic.

Maybe just plopping a button on the worksheet that invokes the code would be
better?

 

[Berry]

The code activate now every time it is recalculating. And that is what
it has to do.

X is saying always I think.
The code have to calculate every time something is changing in column C

There can't be a button on the worksheet because it is been protected.

Berry

 

[Dave Peterson]

Untested, but this might work:

Option Explicit
Private Sub Worksheet_Calculate()
Dim R As Long
Application.EnableEvents = False
For R = 1 To Cells(65535, 3).End(xlUp).Row
Select Case Cells(R, 3).Value
Case "", 0, "C"
Me.Rows(R).Hidden = True
Case Else
Me.Rows(R).Hidden = False
End Select
Next
Application.EnableEvents = True
End Sub


But if the worksheet is protected, you may have trouble in code, too.

 

[Berry]

Goodmorning,

This code is doing the same as my old one! He filter the rows contains
a "C" and a "0" out of the sheet, but I still can't filter.

I can sort ascending an descending with the autofilter. I have no idea
what's wrong.

Berry

 

[Dave Peterson]

If the sheet is protected, then that could be the trouble.

 

[Berry]

It is going to be protected, but now it isn't. It is not necessary to
protect the book. The most important now is the filter is going to
work. Maybe I need a code to use the autofilter in combination with the
code for hiding the rows.
The filter is filtering something but not what I ask to filter.

Greets




Dave Peterson schreef:

 

[Dave Peterson]

I don't have any more suggestions.

Sorry.

It is going to be protected, but now it isn't. It is not necessary to
protect the book. The most important now is the filter is going to
work. Maybe I need a code to use the autofilter in combination with the
code for hiding the rows.
The filter is filtering something but not what I ask to filter.

Greets

Dave Peterson schreef: