Filter isn't working with a VBA Code

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

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



"[email protected]" 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 (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
>
>

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

--
Don Guillett
SalesAid Software
[email protected]
<[email protected]> wrote in message
news:[email protected]...
> 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
>

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:

> Why not use data>filter>custom>does not equal O and does not equal C.
> record the macro
>
> --
> Don Guillett
> SalesAid Software
> [email protected]
> <[email protected]> wrote in message
> news:[email protected]...
> > 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
> >

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

--
Don Guillett
SalesAid Software
[email protected]
<[email protected]> wrote in message
news:[email protected]...
>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:
>
>> Why not use data>filter>custom>does not equal O and does not equal C.
>> record the macro
>>
>> --
>> Don Guillett
>> SalesAid Software
>> [email protected]
>> <[email protected]> wrote in message
>> news:[email protected]...
>> > 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
>> >
>

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"
> >> > 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
> >> >
> >

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
--
Don Guillett
SalesAid Software
[email protected]
<[email protected]> wrote in message
news:[email protected]...
> 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"
>> >> > 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
>> >> >
>> >
>

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!!

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

--
Don Guillett
SalesAid Software
[email protected]
<[email protected]> wrote in message
news:[email protected]...
>
> 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!!
>