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]

Welcome to the forums!

The problem lies in the fact that when you use .Value on a range, both 0 and a blank will return 0. To work around this, we can use Application.CountIf to actually count how many 0s there are, and then test to ensure it returns the proper amount:

Sub Macro2()
'
' Macro2 Macro
Dim X As Long
For X = 14 To Range("B" & Rows.Count).End(xlUp).Row
Application.ScreenUpdating = False
If Application.CountIf(Range("B" & X).Resize(1, 6), "=0") = 6 And Application.CountIf(Range("I" & X).Resize(1, 2), "=0") = 2 Then
    Range("B" & X).EntireRow.Hidden = True
End If
Next X
Application.ScreenUpdating = True
End Sub

Also, I would highly recommend that any time you use a variable as a loop counter (such as X in this code), you dim it as Long. The reason is because Integer can only hold a value up to 32,767; if your spreadsheet is larger than this, your code will error.

 

[nkustura]

Thank you very much. This works.

 

[MrKowz]

Thank you very much. This works.

Glad it works for you. Thank you for the feedback.

 

[nkustura]

I am looking at this code and I don't understand why do you have two Application.Countif's: one for range B:x and one for I:x. Can I combine these two into one application.countIf?

Thanks,

 

[JackDanIce]

It's because the range of values you are evaluating are not consecutive, you wanted to test Bx to Gx and then Ix to Jx - you left Hx out so the assumption was you needed to evaluate two lots of countifs.

 

[MrKowz]

I am looking at this code and I don't understand why do you have two Application.Countif's: one for range B:x and one for I:x. Can I combine these two into one application.countIf?

Thanks,

As JackDanIce points out, your ranges indicated were not contiguous. If you want to include column H in this criteria, you can adjust the code to:

Sub Macro2()
'
' Macro2 Macro
Dim X As Long
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
End If
Next X
Application.ScreenUpdating = True
End Sub

 

[nkustura]

Oooops that was my bad, I meant to include H as well. Now I get it. Thanks

 

[MrKowz]

Oooops that was my bad, I meant to include H as well. Now I get it. Thanks

Not a problem. Cheers

 

[nkustura]

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