Macro to Hide rows that contain only "-'s" or 0's

[bbalch]

Thanks in advance for any help you can provide.

I am very green when it comes to creating macros and I’m hoping someone with more experience can help me out here. I use a budgeting software package to produce P&L’s each month. Unfortunately, you can’t format the reports in the in the software but you can create a template to dump the data into. Two examples of the report can be found at this drop box link: https://www.dropbox.com/sh/jrs8yaas1jyh0ht/AABvZFHK8lOYWyTrwPtEbz9Ja?dl=0

The budgeting software dumps the data into the “Report Data” and “Report Info” tabs, while the “PL wVariances” tab is a template with vlookup’s pointing to the data dump tabs. The template has a vlookup for every account in the chart of accounts and most of the cost centers use 70% of the accounts, so there are several rows with all 0’s or “-“.

I’m trying to create a macro that will hide all of the rows where columns B – M contain only “-“ or 0, see the Desired Report for an example.

Again, thank you for any insights you can give me.

 

[NeonRedSharpie]

Sub groupRows()
    Application.ScreenUpdating = False
    Application.EnableEvents = False
    Application.Calculation = xlCalculationManual
    
    With Sheets("PL wVariances")
        For x = 9 To 149 '.Cells(Rows.Count, 1).End(xlUp).Row Step 1
            If .Cells(x, 1) <> "" And Application.Sum(.Range(.Cells(x, 2), .Cells(x, 13))) = 0 Then
                Rows(x).Group
            End If
        Next x
    End With


    Application.ScreenUpdating = True
    Application.EnableEvents = True
    Application.Calculation = xlCalculationAutomatic
End Sub


Sub hideRows()
    Application.ScreenUpdating = False
    Application.EnableEvents = False
    Application.Calculation = xlCalculationManual
    
    With Sheets("PL wVariances")
        For x = 9 To 149 '.Cells(Rows.Count, 1).End(xlUp).Row Step 1
            If .Cells(x, 1) <> "" And Application.Sum(.Range(.Cells(x, 2), .Cells(x, 13))) = 0 Then
                Rows(x).Hidden = True
            End If
        Next x
    End With


    Application.ScreenUpdating = True
    Application.EnableEvents = True
    Application.Calculation = xlCalculationAutomatic
End Sub

Two options: hide and group. I'd use group.

 

[bbalch]

That is exactly what I was looking for. Thank you!