Change macro to filter Zero not Empty cells

Hello.

I am having difficulties changing this macro.
What it do:
When i double click the target field, it hides all the rows which are empty.
I would like to use it to filter out the rows that has 0 in it.

Can somebody help me out with this please?



Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
If Target.Row < 2 Or Target.Row > 4 Then Exit Sub
If Intersect(Target, Range("P2:CD4")) Is Nothing Then Exit Sub
Cancel = True
Application.ScreenUpdating = False
ActiveSheet.UsedRange.Columns(1).EntireRow.Hidden = False
On Error Resume Next
With Range("A1", Cells(Rows.Count, 1).End(xlUp))
.Offset(4, Target.Column - 1).Resize(.Count - 4).SpecialCells(xlCellTypeBlanks).EntireRow.Hidden = True
End With
Application.ScreenUpdating = True
End Sub

hey guys.
Thank you for your efforts.

I think the global problem may be: the fields i want to filter don't have numbers in it, they have the formula: "=K5*$K$4-L5*$L$4" (adds up numbers by colors, enc... doesn't really m
Unfortunetly non of your advises seem to work

Dave's solution:

ActiveSheet.Range("$J$2:$J$600").AutoFilter Field:=1, Criteria1:="<>0", _
Operator:=xlAnd

Nothing happens, shows everything, still...

Tried to change to:

ActiveSheet.Range("$J$2:$J$600").AutoFilter Field:=1, Criteria1:="<1", _
Operator:=xlAnd

Hides everything (there are matches in the xls)

Oeldere's solution.

Hides everything

Another solution what i use in my other XLS is:


Sub hide_J()
Application.ScreenUpdating = False

Cells.Select
Selection.EntireRow.Hidden = False


With Worksheets("- Stock -")
lrow = .Range("A" & .Rows.Count).End(xlUp).Row
For i = 5 To lrow
If .Range("J" & i).Value = "0" Then
.Rows(i).Hidden = True

End If
Next i
End With

Application.ScreenUpdating = True

End Sub

The problem is that the debugger gives me error when running it highlighting the following area: " lrow ="

If anyone could tell me what is the wrong with it, that would be highly appretiated

Maybe:

Sub Viktor86HUN()
ActiveSheet.Range("$J$2:$J$600").AutoFilter 1, 0
End Sub

Hi John.
Thank you for checking
The problem is the same, it hides everything.

Looks like the fact that these cells don't have numbers in it, just a formula: "=K8*$K$4-L8*$L$4" causes the error, do you know any method to work around this problem?

ok i got it working, i had to save my document in office 97 format..
just figured this function is doing no good to me.

probably i will need a macro for it.

The point is to: Don't show the Zeros

Do you have any idea why this macro gives me the error for: " lrow =" please?
Sub hide_J()
Application.ScreenUpdating = False

Cells.Select
Selection.EntireRow.Hidden = False


With Worksheets("- Stock -")
lrow = .Range("A" & .Rows.Count).End(xlUp).Row
For i = 5 To lrow
If .Range("J" & i).Value = "0" Then
.Rows(i).Hidden = True

End If
Next i
End With

Application.ScreenUpdating = True

End Sub

What i realized so far, by your response is that if i find the right format for these 2 lines, it could and should work

With Range("A1", Cells(Rows.Count, 1).End(xlUp))
.Offset(4, Target.Column - 1).Resize(.Count - 4).SpecialCells(xlCellTypeBlanks).EntireRow.Hidden = True