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