Filter values in certain cells

[russelldt]

I have a Macro button that i need to add a Macro that will perform the following in Column M, Cells M4 to M40.

The cells all contain numbers that are variable based on a formula. Some of the calculations have a "0" as the answer

When i click the button the first time, numbers containing 0 are filtered out.

When i click the button again, the filter is removed, and all values are shown

Many thanks

 

[kevin9999]

See if the following gives you what you want. Change the sheet name to suit.

Option Explicit
Sub HideZeros()
    Application.ScreenUpdating = False
    Dim ws As Worksheet, rng As Range, c As Range
    Set ws = Worksheets("Sheet1")   '<~~ *** Change to actual sheet name ***
    Set rng = ws.Range("M4:M40")
    
    For Each c In rng
        If c.Value = 0 Then
            If c.EntireRow.Hidden = True Then
                c.EntireRow.Hidden = False
            Else
                c.EntireRow.Hidden = True
            End If
        End If
    Next c
    Application.ScreenUpdating = True
End Sub

 

[russelldt]

See if the following gives you what you want. Change the sheet name to suit.

Option Explicit
Sub HideZeros()
    Application.ScreenUpdating = False
    Dim ws As Worksheet, rng As Range, c As Range
    Set ws = Worksheets("Sheet1")   '<~~ *** Change to actual sheet name ***
    Set rng = ws.Range("M4:M40")
   
    For Each c In rng
        If c.Value = 0 Then
            If c.EntireRow.Hidden = True Then
                c.EntireRow.Hidden = False
            Else
                c.EntireRow.Hidden = True
            End If
        End If
    Next c
    Application.ScreenUpdating = True
End Sub

Works perfectly, thank you

 

[kevin9999]

Works perfectly, thank you

You're welcome, and thanks for the feedback

 

[uptightpatroon]

See if the following gives you what you want. Change the sheet name to suit.

Option Explicit
Sub HideZeros()
    Application.ScreenUpdating = False
    Dim ws As Worksheet, rng As Range, c As Range
    Set ws = Worksheets("Sheet1")   '<~~ *** Change to actual sheet name ***
    Set rng = ws.Range("M4:M40")
   
    For Each c In rng
        If c.Value = 0 Then
            If c.EntireRow.Hidden = True Then
                c.EntireRow.Hidden = False
            Else
                c.EntireRow.Hidden = True
            End If
        End If
    Next c
    Application.ScreenUpdating = True
End Sub
[RIGHT]

geometry dash lite[/RIGHT]

Through these topics, I have a deeper understanding of calculations in excel