Another Automatically Hide Rows Question...

[InOverMyH3ad]

Hello All,
I have been searching for threads and while I can find some code for situations similar to mine, I can't seem to re-work the code to fit my specific situation. I just don't have a deep enough understanding of VB to understand how to modify the code. I have fleshed out what I am trying to do below. Any help would be greatly appreciated!

SourceSheet - There are 3 cells on this sheet with input. If they are 0 (their default) then
 I would like certain rows hidden on ModSheet associated with that category. 

ModSheet - A digestable report that grabs numbers from SourceSheet. 
It's pretty big so we want to hide sections without data. 


 By default, hide rows 50-75 when workbook is opened on ModSheet. As the cells on SourceSheet change, hide/unhide specific rows according to info below:

If SourceSheet!H412 <> 0 (this value is a =sum(x+y+z) of several other cells)
    Show rows 50-57 on ModSheet
End If

If SourceSheet!H451 <> 0 (this value is a =sum(x+y+z) of several other cells) 
    Show rows 59-66 on ModSheet
End If

If SourceSheet!H451 <> 0 (this value is a =sum(x+y+z) of several other cells) 
    Show rows 68-75 on ModSheet
End If

 

[Logit]

.

Option Explicit


Private Sub Worksheet_SelectionChange(ByVal Target As Range)
On Error Resume Next
Me.Range("H412").Value = "=SUM(H7,L7,M7)"            '<-- edit these cell references as required
    If Me.Range("H412").Value > 0 Then
        Sheet2.Rows("50:57").EntireRow.Hidden = True
    ElseIf Me.Range("H412").Value = 0 Then
        Sheet2.Rows("50:57").EntireRow.Hidden = False
    End If


Me.Range("H451").Value = "=SUM(H7,L7,M7)"            '<-- edit these cell references as required
    If Me.Range("H451").Value > 0 Then
        Sheet2.Rows("59:66").EntireRow.Hidden = True
    ElseIf Me.Range("H451").Value = 0 Then
        Sheet2.Rows("59:66").EntireRow.Hidden = False
    End If


Me.Range("H451").Value = "=SUM(H7,L7,M7)"            '<-- edit these cell references as required
    If Me.Range("H451").Value > 0 Then
        Sheet2.Rows("59:66").EntireRow.Hidden = True
    ElseIf Me.Range("H451").Value = 0 Then
        Sheet2.Rows("59:66").EntireRow.Hidden = False
    End If


Me.Range("H451").Value = "=SUM(H7,L7,M7)"            '<-- edit these cell references as required
    If Me.Range("H451").Value > 0 Then
        Sheet2.Rows("68:75").EntireRow.Hidden = True
    ElseIf Me.Range("H451").Value = 0 Then
        Sheet2.Rows("68:75").EntireRow.Hidden = False
    End If
End Sub

 

[InOverMyH3ad]

First, thank you so much for taking the time to help me! OK, the code I have is below. I am assuming I have to change Sheet2 to the actual sheet name? Also, if the H4XX cells shift will that break the code or will the code shift with it? Below is what I am trying to get to work:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
On Error Resume Next
Me.Range("H412").Value = "=J379*$J$19+K379*$K$19+L379*$L$19+M379*$M$19+R379*$R$19+S379*$S$19+N379*$N$19+O379*$O$19+P379*$P$19+Q379*$Q$19"            '<-- edit these cell references as required
    If Me.Range("H412").Value > 0 Then
        SL Inbound.Rows("50:57").EntireRow.Hidden = True
    ElseIf Me.Range("H412").Value = 0 Then
        SL Inbound.Rows("50:57").EntireRow.Hidden = False
    End If


Me.Range("H451").Value = "=J418*$J$19+K418*$K$19+L418*$L$19+M418*$M$19+R418*$R$19+S418*$S$19+N418*$N$19+O418*$O$19+P418*$P$19+Q418*$Q$19"            '<-- edit these cell references as required
    If Me.Range("H451").Value > 0 Then
        SL Inbound.Rows("59:66").EntireRow.Hidden = True
    ElseIf Me.Range("H451").Value = 0 Then
        SL Inbound.Rows("59:66").EntireRow.Hidden = False
    End If




Me.Range("H490").Value = "=J457*$J$19+K457*$K$19+L457*$L$19+M457*$M$19+R457*$R$19+S457*$S$19+N457*$N$19+O457*$O$19+P457*$P$19+Q457*$Q$19"            '<-- edit these cell references as required
    If Me.Range("H490").Value > 0 Then
        SL Inbound.Rows("68:75").EntireRow.Hidden = True
    ElseIf Me.Range("H451").Value = 0 Then
        SL Inbound.Rows("68:75").EntireRow.Hidden = False
    End If
End Sub

 

[Logit]

.
Looking in the VBE, upper left side, you see the sheets listed.

Sheet1(Sheet1)
Sheet2(Sheet2)
Sheet3(Sheet3)

In the macro, "Sheet2" refers to the sheet reference before the ( )'s

A "sheet name" is what is included inside the ( ) 's

You will have less of a chance of an error if you continue to use the sheet reference "before the ( ) 's ", because that doesn't change.

You can edit the " H4XX " to any cell reference you want. It will not 'break' the code.

 

[InOverMyH3ad]

I apologize for the delay in response. Things have been crazy around here! I have the code working! My concern is, if I insert a row above the cell that his triggering the hide/show function, will the code stop working? So the first bit of code is looking at H412 for a value. If I insert a row above H412 so that now H413 has the value I'm looking for will the VBA code shift with the reference cell?

 

[Logit]

.
No