Excel filter does not working anymore

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

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 wrote:
>
> 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

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

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 wrote:
>
> 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

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

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 wrote:
>
> 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