Hide and unhide multiple rows based on specific cell values (different cells hide different rows)

[grfbro1]

All - Trying to create a code that hides a row or multiple rows based on certain cells having a 0. I tried If statements and other .hidden functions but I can't get certain rows to hide all at once or to do everything below. The sheet is below and I'm trying to have the logic do the below. All the values in the sheet are pulling from another sheet.

Hide rows 10 - 23 each independently if value in column B is 0 in the row

Hide rows 24 - 33 if C26 is 0

Hide rows 40 - 47 each independently if value in column B is 0 in the row

Hide rows 52 - 53 if B40 is 0

Hide rows 57 - 62 if B43 is 0

Hide rows 36 - 39 if SUM(B40:B47) is 0

If all the numbers change to a number not 0 and the code is run the rows will unhide.

 

[Joe4]

Try this:

Sub MyHideRowsMacro()

    Dim r As Long
    Dim rng As Range
    
    Application.ScreenUpdating = False

'   Check 1
    For r = 10 To 23
        Rows(r).Hidden = (Cells(r, "B").Value = 0)
    Next r
    
'   Check 2
    Rows("24:33").Hidden = (Range("C26").Value = 0)
    
'   Check 3
    For r = 40 To 47
        Rows(r).Hidden = (Cells(r, "B").Value = 0)
    Next r
    
'   Check 4
    Rows("52:53").Hidden = (Range("B40").Value = 0)
    
'   Check 5
    Rows("57:62").Hidden = (Range("B43").Value = 0)
    
'   Check 6
    Set rng = Range("B40:B47")
    Rows("57:62").Hidden = (Application.WorksheetFunction.Sum(rng) = 0)
    
    Application.ScreenUpdating = True
        
End Sub

 

[grfbro1]

That worked perfectly, thanks.

 

[Joe4]

You are welcome.

 

[grfbro1]

You are welcome.

You are welcome.

For some reason, the Check 3 isn't working and the rows between 40 and 47 still display when there is a 0 in column B. I was playing around with multiple iterations to create various rows with 0 and it only works if all of the rows between 40 and 47 are 0. Everything else works great.

 

[Joe4]

For some reason, the Check 3 isn't working and the rows between 40 and 47 still display when there is a 0 in column B. I was playing around with multiple iterations to create various rows with 0 and it only works if all of the rows between 40 and 47 are 0. Everything else works great.

It seems to work just fine for me.

Here is a simple example.

Data before running VBA code:

After running VBA code:

That seems to do exactly what you asked, does it not?

Can you show me a before and after picture when you try it?

 

[grfbro1]

I do feel like it was working (maybe I was trying to zero out everything) this morning but then it was just that Check 3 section that wasn't working unless all values were zero. I started from scratch and redid the code and it seems to be working again. Strange that the code was the exact same so guessing I was doing something wrong. Appreciate the help.

 

[Joe4]

You are welcome.
Glad I was able to help.

Note: it is always best to use Copy/Paste in getting the code from here to your workbook, in case you were trying to type it manually. I have seen typos trip people up!
Also note that sometimes you may get unexpected behavior if you have numbers entered as text values on your sheet.
Those are probably the two most common reasons I see for things like this not behaving the way you expect.