Hide Zero Rows but NOT Blank Rows

[nkustura]

Hi,
I have to write a macro to hide all rows in range B:J where values in all cells in this range are zero, but if the cell is blank ( I have some blank cells between sections) I don't want it to be hidden.

All help will be greatly appreciated.

Here is the code I have now, but it's hiding blank rows as well as zero value rows.

Sub Macro2()
'
' Macro2 Macro
Dim X As Integer
For X = 14 To Range("B" & Rows.Count).End(xlUp).Row
Application.ScreenUpdating = False
If Range("B" & X).Value = 0 And Range("C" & X).Value = 0 And Range("D" & X).Value = 0 And Range("E" & X).Value = 0 And _
Range("F" & X).Value = 0 And Range("G" & X).Value = 0 And Range("I" & X).Value = 0 And _
Range("J" & X).Value = 0 Then
Range("B" & X).EntireRow.Hidden = True
End If

Next X
End Sub


Thanks,
Nermina

 

[MrKowz]

Hi,

Is there a way to insert a msg box at the end of this macro that will show how many rows were hidden?

Thanks,
Nermina

Sure is! We just need to add a counter in the code, and refer to that counter when we invoke the message box:

Sub Macro2()
'
' Macro2 Macro
Dim X           As Long, _
    HideCount   As Long

Application.ScreenUpdating = False

For X = 14 To Range("B" & Rows.Count).End(xlUp).Row
    If Application.CountIf(Range("B" & X).Resize(1, 9), "=0") = 9 Then
        Range("B" & X).EntireRow.Hidden = True
        HideCount = HideCount + 1
    End If
Next X

MsgBox HideCount & " rows hidden."

Application.ScreenUpdating = True
End Sub

 

[taigovinda]

Just add a counter and increment it each time you hide a row:

Sub Macro2()
'
' Macro2 Macro
Dim X As Long, i as long
i=0
For X = 14 To Range("B" & Rows.Count).End(xlUp).Row
Application.ScreenUpdating = False
If Application.CountIf(Range("B" & X).Resize(1, 9), "=0") = 9 Then
    Range("B" & X).EntireRow.Hidden = True
    i=i+1
End If
Next X
Application.ScreenUpdating = True
msgbox i & "rows hidden.", vbinformation, "Success"
End Sub

edit: Yeah, what MrKowz said!

 

[nkustura]

Thank you so much. You guys are awesome!

 

[MrKowz]

Thank you so much. You guys are awesome!

We try.
Thanks for the feedback!