Filter isn't working with a VBA Code

[blommerse]

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 (a part of) my sheet looks:


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

 

[CLR]

You could get to where you want by just forgetting the macro and using the
AutoFilter to filter what you want, and also including filtering out the 0's
and C's in that column with the Custom selection > DoesNotEqual......0 > AND

DoesNotEqual.....C

Vaya con Dios,
Chuck, CABGx3

 

[Don Guillett]

Why not use data>filter>custom>does not equal O and does not equal C.
record the macro

 

[blommerse]

I didn't do this because it has to filter automaticly alle the 0 and
the C out of the sheet, and then hide them.

Now my filter is not filtering correcty but my code is working
correcty.

The filter is not working in combination with a code it is look like.

Don Guillett schreef:

 

[Don Guillett]

If your code works properly so would code to custom autofilter. Is your O a
zero or a letter O? If so
not equal 0 instead of not equal O

 

[blommerse]

The 0 is a zero. It is a result from a splitsing. It is look like this:

J J0604CFI
T T0605CFI
H H0606CFI
0 0
C C0606CFI

In de first column I give the order to pick the first character from
the second column
So in the second column I give the order to look pick cell A2 from
book2!
If there's nothing the formule gives a zero.

I want to hide the rows with a zero and a "C"

 

[Don Guillett]

Then just use the custom filter for 0 (zero) and C

Sub CustomFilter()
Range("a1").AutoFilter Field:=1, Criteria1:="<>0", _
Operator:=xlAnd, Criteria2:="<>C"
End Sub

 

[blommerse]

This one doesn't work at all !

Column C is the column which the code have to look at. I changed it in
the code, but it still don't work.

My filter is working good, but it has to work both!!

 

[Don Guillett]

I just sent you a sample workbook. It does work.