Macro to Hide zero rows in Multiple Sheets

[compd]

Hi Guys,

I was hoping I could get some assistance. Basically what I required is to hide zero rows on multiple sheets within a book based on if the sum of the different columns = 0

So as an example I have data from Column E to Column K going down to 400 rows, so if in row 5 column e to column K the sum of all columns equal to zero I would like the row hiden. The range will be the same for every sheet.

I have looked at the below macro which works good, but it will only hide zero values if there is no Data in Column K only. What I require is a sum as oppose to just looking at one column.

Sub HideRows()
For Each sh In ThisWorkbook.Worksheets
For Each c In sh.Range("e5:k400")
If c.Value = 0 Then
c.EntireRow.Hidden = True
Else
c.EntireRow.Hidden = False
End If
Next c
Next sh
End Sub

 

[AlphaFrog]

Sub HideRows()
    Dim sh As Worksheet
    Dim i As Long
    For Each sh In ThisWorkbook.Worksheets
        For i = 5 To 400
            sh.Rows(i).Hidden = Application.Sum(sh.Range("E" & i).Resize(, 7)) = 0
        Next i
    Next sh
End Sub

 

[compd]

Hi,

That seemed to have worked, however it gives me an a "run time error 13" "Type Mismatch" and when I go to debug it takes me to

sh.Rows(i).Hidden = Application.Sum(sh.Range("E" & i).Resize(, 7)) = 0

Not sure if you could assist? appreciate your help.

Thanks,
compd

 

[compd]

Hi,

I have managed to figure this one out, the reason I got the error message was due to the fact that there was some DIV/0 errors within the given area. I have adjusted the code to include "On Error Resume Next" and it works fine.

Just one question though, if you could assist. How can I run the whole VBA code only on specific sheets within a workbook? i.e. the workbook contains 5 sheets, i would like the code to ignore the last two sheets.

Once again thanks for your help.

 

[AlphaFrog]

For Each sh In Sheets(Array("Sheet1", "Sheet2", "Sheet3"))

Change the three sheet names to suit.

 

[CD_Sage]

Hello all,

I'm tyring to integrate the following conditions into the below code. Any help would be appreciated!

I only want the Row to hide if...

If IsNumeric(cell.Value) And Not IsEmpty(cell) And Not cell.Font.Bold Then

Sub HideRows()
    Dim sh As Worksheet
    Dim i As Long
    For Each sh In ThisWorkbook.Worksheets
        For i = 5 To 400
            sh.Rows(i).Hidden = Application.Sum(sh.Range("E" & i).Resize(, 7)) = 0
        Next i
    Next sh
End Sub

~Chris

 

[AlphaFrog]

Hi Chris and welcome to the forum. Well done on first searching for a solution.

Maybe try something like this. Change the column letter to suit.

[COLOR=darkblue]Sub[/COLOR] HideRows()
    [COLOR=darkblue]Dim[/COLOR] sh [COLOR=darkblue]As[/COLOR] Worksheet
    [COLOR=darkblue]Dim[/COLOR] i [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Long[/COLOR]
    [COLOR=darkblue]For[/COLOR] [COLOR=darkblue]Each[/COLOR] sh [COLOR=darkblue]In[/COLOR] ThisWorkbook.Worksheets
        [COLOR=darkblue]For[/COLOR] i = 5 [COLOR=darkblue]To[/COLOR] 400
            [COLOR=darkblue]With[/COLOR] sh.Range("E" & i)
                sh.Rows(i).Hidden = IsNumeric(.Value) And .Value <> "" And [COLOR=darkblue]Not[/COLOR] .Font.Bold
            [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]With[/COLOR]
        [COLOR=darkblue]Next[/COLOR] i
    [COLOR=darkblue]Next[/COLOR] sh
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Sub[/COLOR]

 

[CD_Sage]

Apologies, I'm trying to hide the row, if it's sum is zero but not if it's blank, in bold or non numeric.

Appreciate the help!

 

[99moorem]

Hi CD_Stage,

This may help you get on your way this works by passing it a range but could easily be adapted to work for each sheet. works for a series of 1's and 0's. 0 being hide and 1 being unhide

Public Sub HideRows(list1 As Range)
    Application.ScreenUpdating = False
    Application.EnableEvents = False
    Dim cellsToHide As Range
    Set cellsToHide = Nothing
    Dim cellsToUnHide As Range
    Set cellsToUnHide = Nothing
    
    Dim cell As Range
    For Each cell In list1
        If cell.Value = 0 Then
            If cellsToHide Is Nothing Then
                Set cellsToHide = cell
            Else
                Set cellsToHide = Union(cellsToHide, cell)
            End If
        ElseIf cell.Value = 1 Then
            If cellsToUnHide Is Nothing Then
                Set cellsToUnHide = cell
            Else
                Set cellsToUnHide = Union(cellsToUnHide, cell)
            End If
        End If
    Next cell
    If Not (cellsToHide Is Nothing) Then cellsToHide.EntireRow.Hidden = True
    If Not (cellsToUnHide Is Nothing) Then cellsToUnHide.EntireRow.Hidden = False
    Application.ScreenUpdating = True
    Application.EnableEvents = True
End Sub

Wanted to put this somewhere as this is much faster than the traditional .hidden method.

Marc