Hiding rows with zero values

[cubiclemonkey]

Good Afternoon,

I have a report which has an output that pulls data from another tab in the workbook using the SUMIFs formula based on the Department selected in cell A1. Depending on the Department selected, some of the rows will have zero values. Is there a way to write a macro which will hide the rows with zero values after selecting the Department? I should also add the rows with zero values fluctuate so there is no consistency to it as it depends on which Department is being selected which then fills the rows.

Example is the Cookie Department:

And the Donut Department:

Thanks,
Ahmed

 

[cubiclemonkey]

Forget about the question in Post #8. You're working with a dropdown I see.

Does this work for you?

Sub AAAAA()
Dim ws As Worksheet, lr As Long
lr = Cells(Rows.Count, 6).End(xlUp).Row
Set ws = ActiveSheet
Application.ScreenUpdating = False
    With ws
        .AutoFilterMode = False
        .Range("F6:F" & lr).AutoFilter 1, "<>0"
    End With
Application.ScreenUpdating = True
End Sub

Thank you for sharing, unfortunately it shows the filter which won't pass with my supervisor.

 

[cubiclemonkey]

The following will work automatically whenever you change the selection in cell A1 of the summary sheet. You'll need to put this code in the sheet code area of the Summary sheet. Right-click the sheet tab name, select View Code, and put the code in the blank area to the right of the screen. I've added an updated file for your perusal with the code already added.

Example_DummyData V2.xlsm

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Range("A1"), Target) Is Nothing Then
        Application.EnableEvents = False
        With Me.Range("F5:F" & Cells(Rows.Count, "F").End(xlUp).Row - 1)
            .AutoFilter 1, "<>0", 1, "<>", 0
        End With
    End If
    Application.EnableEvents = True
End Sub

Hi Kevin,

Thank you this works except for "Cookies" and "Donuts" which shows row 5 even though it's blank. Another complication is what if I have a second set of data below and there is a space in between the first set of rows and the next set of rows?

Thanks,
Ahmed

 

[kevin9999]

Thank you this works except for "Cookies" and "Donuts" which shows row 5 even though it's blank.

That's because the file you shared (and your original post) has row 5 as a blank row, whereas post #12 has data in that row? Which is correct?

what if I have a second set of data below and there is a space in between the first set of rows and the next set of rows?

If you mean you have your data in 2 (or more) distinct ranges/areas, then please provide a sample of what it would look like as that would require a different approach entirely.

 

[kevin9999]

Try this instead

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Range("A1"), Target) Is Nothing Then
        Application.EnableEvents = False
        With Me.Range("F3:F" & Cells(Rows.Count, "F").End(xlUp).Row - 1)
            .AutoFilter 1, "<>0", 1, "<>", 0
        End With
    End If
    Application.EnableEvents = True
End SubPrivate Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Range("A1"), Target) Is Nothing Then
        Application.EnableEvents = False
        With Me.Range("F3:F" & Cells(Rows.Count, "F").End(xlUp).Row - 1)
            .AutoFilter 1, "<>0", 1, "<>", 0
        End With
    End If
    Application.EnableEvents = True
End Sub

 

[cubiclemonkey]

Try this instead

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Range("A1"), Target) Is Nothing Then
        Application.EnableEvents = False
        With Me.Range("F3:F" & Cells(Rows.Count, "F").End(xlUp).Row - 1)
            .AutoFilter 1, "<>0", 1, "<>", 0
        End With
    End If
    Application.EnableEvents = True
End SubPrivate Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Range("A1"), Target) Is Nothing Then
        Application.EnableEvents = False
        With Me.Range("F3:F" & Cells(Rows.Count, "F").End(xlUp).Row - 1)
            .AutoFilter 1, "<>0", 1, "<>", 0
        End With
    End If
    Application.EnableEvents = True
End Sub

Thanks Kevin will try. I was able to "trick" excel by applying a flag in another column with your last code but will try this too.

 

[kevin9999]

Happy to help & thanks for the feedback