VBA/Macro Help- Hiding Rows Based on Cell Value

[woahfee]

Hello-

I am trying to use a simple macro to hide rows based on the value of three different cells. The code tat I am currently using is below. The first portion works correctly, however when I copy it to use on the other required rows it doesn't work. Can someone please assist? I'm new to VBA. Thanks!


Private Sub Worksheet_Change(ByVal Target As Range)

If Range("b23").Value = "FS" Then
Rows("25:25").EntireRow.Hidden = True
ElseIf Range("B23").Value = "NNN" Then
Rows("25:25").EntireRow.Hidden = True
ElseIf Range("B23").Value = "IG" Then
Rows("25:25").EntireRow.Hidden = False
ElseIf Range("B23").Value = "Net of Electric" Then
Rows("25:25").EntireRow.Hidden = False
End If
End Sub


Private Sub Worksheet_Change2(ByVal Target As Range)

If Range("B11").Value = "FS" Then
Rows("13:13").EntireRow.Hidden = True
ElseIf Range("B11").Value = "NNN" Then
Rows("13:13").EntireRow.Hidden = True
ElseIf Range("B11").Value = "IG" Then
Rows("13:13").EntireRow.Hidden = False
ElseIf Range("B11").Value = "Net of Electric" Then
Rows("13:13").EntireRow.Hidden = False
End If
End Sub




Private Sub Worksheet_Change3(ByVal Target As Range)

If Range("B35").Value = "FS" Then
Rows("37:37").EntireRow.Hidden = True
ElseIf Range("B35").Value = "NNN" Then
Rows("37:37").EntireRow.Hidden = True
ElseIf Range("B35").Value = "IG" Then
Rows("37:37").EntireRow.Hidden = False
ElseIf Range("B35").Value = "Net of Electric" Then
Rows("37:37").EntireRow.Hidden = False
End If
End Sub

 

[Joe4]

Welcome to the Board!

Worksheet Change events procedures run automatically based on some event happening.
As such, there are some strict requirements surrounding them, such as:
- They MUST be named a certain way. You cannot change the name of them (i.e. by placing a number after the word "Change").
- They must be placed in the correct module.

Also, as with any VBA procedure, you cannot have multiple procedures in the same module with the same name.

So, what it all boils down to is you need to have one, single Worksheet_Change procedure. Just have three different blocks of code in it.

By the way, I don't know if it is your intention, but you have it so that your code will run EVERY time any cell on your sheet is changed. That may be unnecessary.
If you only want it to run when B11, B23, or B35 is manually updated, you need to include the use of "Target" in your code.
If you would like help with that, please confirm how B11, B23, and B35 are changed.

 

[Fluff]

If Cells B11, B25 & B35 are updated manually then you could try this (it replaces all of your posted code)

Private Sub Worksheet_Change(ByVal Target As Range)

    If Target.Cells.Count > 1 Then Exit Sub
    If Not Intersect(Target, Range("B11,B23,B35")) Is Nothing Then
        Select Case UCase(Target.Value)
            Case "FS", "NNN"
                Target.Offset(2).EntireRow.Hidden = True
            Case "IG", "Net of Electric"
                Target.Offset(2).EntireRow.Hidden = False
        End Select
    End If

End Sub

 

[woahfee]

Welcome to the Board!

Worksheet Change events procedures run automatically based on some event happening.
As such, there are some strict requirements surrounding them, such as:
- They MUST be named a certain way. You cannot change the name of them (i.e. by placing a number after the word "Change").
- They must be placed in the correct module.

Also, as with any VBA procedure, you cannot have multiple procedures in the same module with the same name.

So, what it all boils down to is you need to have one, single Worksheet_Change procedure. Just have three different blocks of code in it.

By the way, I don't know if it is your intention, but you have it so that your code will run EVERY time any cell on your sheet is changed. That may be unnecessary.
If you only want it to run when B11, B23, or B35 is manually updated, you need to include the use of "Target" in your code.
If you would like help with that, please confirm how B11, B23, and B35 are changed.

B11, B23, and B35 are changed from a drop down. I used data validation to have the dropdown have 4 options- FS, IG, NNN, Net of Electric.

 

[Joe4]

B11, B23, and B35 are changed from a drop down. I used data validation to have the dropdown have 4 options- FS, IG, NNN, Net of Electric.

OK, then Fluff's code should do the trick for you.
Try it out and see how it works.

 

[woahfee]

Thanks! It does seem to work for three of the options, however when I choose Net of Electric it does not work properly? Going from FS -> IG works but FS->Net of Electric does not force the hidden row to become unhidden?

 

[Joe4]

I thought it was the use of UCASE, and then he has it in Proper Case.
However, that still didn't work. Perhaps it does not like the spaces.

Try:

Private Sub Worksheet_Change(ByVal Target As Range)

    If Target.Cells.Count > 1 Then Exit Sub
    If Not Intersect(Target, Range("B11,B23,B35")) Is Nothing Then
        Select Case UCase(Trim(Left(Target, 3)))
            Case "FS", "NNN"
                Target.Offset(2).EntireRow.Hidden = True
            Case "IG", "NET"
                Target.Offset(2).EntireRow.Hidden = False
        End Select
    End If

End Sub

It worked for me.

 

[Fluff]

My mistake, I was originally converting the target value to Upper case, but forgot to do that in the select case.
As your values are from a drop down. try

Private Sub Worksheet_Change(ByVal Target As Range)

    If Target.Cells.Count > 1 Then Exit Sub
    If Not Intersect(Target, Range("B11,B23,B35")) Is Nothing Then
        Select Case Target.Value
            Case "FS", "NNN"
                Target.Offset(2).EntireRow.Hidden = True
            Case "IG", "Net of Electric"
                Target.Offset(2).EntireRow.Hidden = False
        End Select
    End If

End Sub

 

[woahfee]

That worked! Thanks both!

 

[Fluff]

Glad we could help & thanks for the feedback