Hide Rows Based on Another Sheets Values

[Daylon]

Hi, I'm trying to hide rows on an excel worksheet ("SOW & RHS") based on whether or not a specific cell of another worksheet ("Labor & Material") is empty. i.e.: If cell C5 of worksheet ("Labor & Material") has any value (if it is not empty) , row 24 on worksheet ("SOW & RHS") should be visible. If it has no value (is empty), row 24 on worksheet ("SOW & RHS") should be hidden. The code should repeat itself, so that row 24 depends on C5, row 25 on C6 and so on.

I would like the rows to be visible/hidden automatically as information is added to worksheet ("Labor & Material").

Thank you,

Here is a sample code I tried:

 

[Joe4]

If you want to look at cells C5, C6, C7, etc, why are you looking at cell "C5:F5" in your code?
What cells EXACTLY do you want to look at?
Is it just the values in column C, starting on row 5?
Is there any limit to how far down column C you want to look?

Also note that this code will only run when you update a value in that column, and it will only check the cell just updated, and not the other cells in your range.
And it will only run when cells are manually updated (will not run if the value is changed by a formula).
Is that how you want it to work?

One last note, when posting your VBA code, please copy and paste the code and use Code Tags around it, as described here: How to Post Your VBA Code
Please do not paste pictures/images of your code. That does not do us much good, as we cannot copy/paste that into the Excel VB Editor window.

 

[Daylon]

If you want to look at cells C5, C6, C7, etc, why are you looking at cell "C5:F5" in your code? This was an accident and it should be "C5"

What cells EXACTLY do you want to look at? I need it to look at "C5" on sheet "Labor & Material", which will be hard keyed by the user.

Is there any limit to how far down column C you want to look? C5:C39 and then pick back up at C45:C80. This will repeat until C381:C416. On the labor and material sheet we have "blocks". Block 1 is C5:C39 and block 2 is C45:C80 and so on. Each block has two toggle buttons, Toggle 1 will open the next block or close the next block if its not needed and then toggle 2 will remove blank cells from the block.

The intention is for the SOW & RHS sheet to mirror the Labor & Material sheet in real time without manually running the code.

Private Sub Worksheet_change(ByVal Target As Range)

If Intersect(Target, Range("C5")) Is Nothing Then Exit Sub

If Target.Value = "" Then

Sheets("SOW & RHS").Rows(A24).EntireRow.Hidden = True

Else

Sheets("SOW & RHS").Rows(A24).EntireRow.Hidden = False

End If

End Sub

 

[Joe4]

So, to follow your pattern, for the first block, C5 would affect row 24 on the other sheet, and the last cell in that "block", C39, should affect row 58 on the other sheet, right?
Then when we pick up the next block (C45:C80), what row on the other sheet will C45 be affecting?

 

[Daylon]

Hey Joe,

Block 1 on Labor and Material ranges from C5:C39
Block 1 on SOW & RHS ranges from C24:C58
Block 2 on Labor and Material ranges from C46:C80
Block 2 on SOW and RHS ranges from C62:C96
Block 3 on Labor and Material ranges from C88:C122
Block 3 on SOW and RHS ranges from C100:C134

This pattern continues all the way to block 10

Block 10 on Labor and Material ranges from C382:C416
Block 10 on SOW and RHS ranges from C366:C400

Sheet Names
Labor & Material = Labor and Material
SOW & RHS = SOW and RHS

 

[Joe4]

Hmmm, this could be difficult or problematic if you do not have consistency in your jumps between blocks.

Block 1 on Labor and Material ranges from C5:C39
Block 1 on SOW & RHS ranges from C24:C58
Block 2 on Labor and Material ranges from C46:C80
Block 2 on SOW and RHS ranges from C62:C96
Block 3 on Labor and Material ranges from C88:C122

If that is accurate, your "jump" between Block 1 and Block 2 is 41 rows,
but your "jump" between Block 2 and Block 3 is 42 rows.

I am hoping that is just a typo on your part.
Can you clarify?

 

[Daylon]

Joe,

Our spacing between blocks 1 and 2 was indeed off by 1. Thanks for the catch. The block pattern should be as follows:

Block 1 on Labor and Material ranges from C5:C39
Block 1 on SOW & RHS ranges from C24:C58
Block 2 on Labor and Material ranges from C47:C81
Block 2 on SOW and RHS ranges from C62:C96
Block 3 on Labor and Material ranges from C89:C123
Block 3 on SOW and RHS ranges from C100:C134

This pattern continues all the way to block 10

Block 10 on Labor and Material ranges from C383:C417
Block 10 on SOW and RHS ranges from C366:C400

 

[Joe4]

Try this:

Private Sub Worksheet_Change(ByVal Target As Range)
    
    Dim i As Long
    Dim rng As Range
    Dim cell As Range
    Dim x As Long   'offset between first row in block between two sheets

'   Exit if update to any column other than column C
   If Target.Column <> 3 Then Exit Sub

'   Set initial range to check
    Set rng = Range("C5:C39")
'   Set initial offset (difference in starting rows on blocks in each sheet)
    x = 19

'   Loop through all cells in Target
    For Each cell In Intersect(Target, Columns("C:C"))
'       Loop through 10 blocks
        For i = 1 To 10
'           See it cell is in this block
            If Not Intersect(cell, rng) Is Nothing Then
'               Hide/unhide row on "SOW & RHS" sheet based on value
                Sheets("SOW & RHS").Rows(cell.Row + x).Hidden = (cell.Value = "")
'               Exit loop if cell found after handling
                Exit For
            End If
'           Reset range and offset for next block
            Set rng = rng.Offset(42, 0)
            x = x - 4
        Next i
    Next cell
    
End Sub

 

[Daylon]

Hey Joe, we placed your code inside of the excel document and when we run it nothing happens. When we alter the code to what we believe is needed it crashes.

We do not dabble in VBA everyday, so understanding the second half of the code is a little difficult. Could you offer some explanation or guidance on what its doing and why?

 

[Joe4]

What is the name of the module where you placed the code.
It MUST be placed in the "Labor & Material" sheet module in VBA - if you place it anywhere else, it will not work!
Also note that this code will only automatically fire when manual changes are made to column C on this sheet. If the values in column C are formulas whose values are changing, the code will not fire automatically.

I would recommend NOT making any changes to the code until you can test out the original conditions you specified and verified that they work.
Once those work, you can start messing around with it. If you get to that point, and cannot make the necessary adjustments, please post your most current version of the code (with your changes), and explain exactly what you are trying to change.