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

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

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:

Please Login or Register  to view this content.

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?

I tested it with a Formula which returned a 0 or another value, and it filtered?

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

ok some googleing solved my problem

it was ez:

Sub HideRows()
Dim cell As Range
For Each cell In Range("J19:J33")
If Not isEmpty(cell) Then
If cell.Value = 0 Then
cell.EntireRow.Hidden = True
End If
End If
Next
End Sub

this code saved my life, thanks for all your support guys