Hiding Rows based on Multiple Text Values

[nrp711]

Hi,

I'm quite new at VBA and I'm trying to hide certain sections of a spreadsheet if two cells don't equal a certain text i.e (multiple items). The intial code of developed is:

If Range("B5").Activate = "(Multiple Items)"
and Range("B6").Activate ="(Multiple Items)"
Then
Rows("56:58").Select
Selection.EntireRow.Hidden = False

Else
Rows("56:58").Select
Selection.EntireRow.Hidden = True


However this code breaks down at the first line. Does anyone know the correct code and also what needs to be done so the code activates automatically?

Many thanks,

Neil

 

[AlphaFrog]

Hi and welcome.

Try something like this...

Rows("56:58").EntireRow.Hidden = _
    Range("B5").Value = "(Multiple Items)" And _
    Range("B6").Value = "(Multiple Items)"

 

[nrp711]

Hi and welcome.

Try something like this...

Rows("56:58").EntireRow.Hidden = _
    Range("B5").Value = "(Multiple Items)" And _
    Range("B6").Value = "(Multiple Items)"

Thanks for your quick reply. I actually need the code to unhide the rows if the cells values both equal (Multiple Items), otherwise I need them rows to be hidden.

How could I change the code to reflect this?

 

[AlphaFrog]

Rows("56:58").EntireRow.Hidden = _
     Range("B5").Value <> "(Multiple Items)" Or _
     Range("B6").Value <> "(Multiple Items)"

 

[nrp711]

Rows("56:58").EntireRow.Hidden = _
     Range("B5").Value <> "(Multiple Items)" Or _
     Range("B6").Value <> "(Multiple Items)"

Thanks so much, is there an additional code I need to write to allow the above code to automatically run, so I don't have to prompt the macro each time I change the value in cells B5 and B6

 

[AlphaFrog]

Thanks so much, is there an additional code I need to write to allow the above code to automatically run, so I don't have to prompt the macro each time I change the value in cells B5 and B6

It depends on how cells B5 and B6 get their values. Does the user set the values by typing it in, or do they have formulas?

This will work if the user enters values (not formulas) in B5 or B6.

• Right-click on the sheet tab that has these two cells
• Select from the pop-up menu View Code
• Paste the code below in the VBA edit window.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("B5:B6")) Is Nothing Then
        Rows("56:58").EntireRow.Hidden = _
             Range("B5").Value <> "(Multiple Items)" Or _
             Range("B6").Value <> "(Multiple Items)"
    End If
End Sub

 

[nrp711]

It depends on how cells B5 and B6 get their values. Does the user set the values by typing it in, or do they have formulas?


This will work if the user enters values (not formulas) in B5 or B6.

• Right-click on the sheet tab that has these two cells
• Select from the pop-up menu View Code
• Paste the code below in the VBA edit window.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("B5:B6")) Is Nothing Then
        Rows("56:58").EntireRow.Hidden = _
             Range("B5").Value <> "(Multiple Items)" Or _
             Range("B6").Value <> "(Multiple Items)"
    End If
End Sub

Thanks so much, it works perfectly. Looks like I got a long way to go to understand VBA code