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

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

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

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

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:

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

I don't have any more suggestions.

Sorry.

Berry wrote:
>
> 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:
>
> > If the sheet is protected, then that could be the trouble.
> >

--

Dave Peterson