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

 

[kevin9999]

Could you post a copy of your sheet using the XL2BB - Excel Range to BBCode, or alternatively, share your file via Dropbox, Google Drive or similar file sharing website?

 

[cubiclemonkey]

Hi Kevin, I created a version with dummy data that you can access it here: Example

 

[kevin9999]

When I try to access the file I get the message "Unauthorized"! Make sure you made the file accessible to anyone with the link.

 

[cubiclemonkey]

Sorry about that should be good now.

 

[cubiclemonkey]

While trying to come up with a solution I came up with the following, however, the issue is that I need to identify each row to hide and the main data set is much larger:

Sub hiderows()

Rows("5:20").EntireRow.Hidden = False

x = Range("B1").value

Select Case x

Case 1: Range("11:11,13:13,16:16").EntireRow.Hidden = True
Case 2: Range("7:8,11:11,14:14,16:16").EntireRow.Hidden = True
Case 3: Range("6:6,8:8,11:11,14:14,18:18").EntireRow.Hidden = True
Case 4: Range("6:6,9:9,11:11,13:13,15:15,18:19").EntireRow.Hidden = True

End Select

End Sub

 

[kevin9999]

Sorry about that should be good now.

Thank you

 

[jolivanes]

Are all departments in different sheets?

 

[jolivanes]

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

 

[kevin9999]

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