Need help to show/hide rows based on values from a cell

**concurmgr** · 12-14-2012, 07:00 PM

Hello! I was wondering if anyone might be able to help me? I'm trying to find a way to accomplish the following:

If cell A7 is null, hide rows 11-32
If cell A7 = no, hide rows 11-32
If cell A7 = yes, show rows 11-32

Would anyone be able to assist?

Thank you!

**HaHoBe** · 12-15-2012, 07:25 AM

Hi, concurmgr,

you need to tell us how the entry in that cell is made: directly from a user or via Formumla?

For a direct entry you could use a static macro like this

Sub concurmgr_HideRows()
Rows("11:32").Hidden = LCase(Range("A7").Value) = "yes"
End Sub

Evaluation is made on Range("A7"), and only if the entry is yes the rows will be shown.

You could use an event to do so (code goes behind the sheet):

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
If Not Intersect(Target, Range("A7")) Is Nothing Then
  Rows("11:32").Hidden = LCase(Target) = "yes"
End If
End Sub

Ciao,
Holger

**concurmgr** · 12-17-2012, 04:15 PM

Hello Holger! Thank you so much for your reply! I apologize for not indicating how A7 would be populated.

A7 is driven by a validation list. So the user will manually select one of three values in that cell based on the list. Does that impact the suggested code above? Thank you!

**HaHoBe** · 12-18-2012, 12:39 AM

Hi, concurmgr,

Data/Validation will trigger the Worksheet_Change-Event so the provided code should work fine with that.

Ciao,
Holger

**concurmgr** · 12-18-2012, 09:19 PM

Thank you Holger! For some reason, the code isn't working. I copied and pasted it directly, but when I select any value in A7, it exposes all rows. So sorry to ask, but do you have any suggestions. Perhaps I am missing something by doing a direct copy/paste of the code above?

**HaHoBe** · 12-19-2012, 12:47 AM

Hi, concurmgr,

only suggestion right now would be that the code is not where it´s got to be placed: behind the sheet where the event is triggered. Maybe you can have a look at the sample attached.

Ciao,
Holger

**concurmgr** · 12-19-2012, 02:39 PM

This is great! I see that the code works, only in reverse. (It is hiding the rows when the value is "yes".) Is there a way to reverse that? It should only expose the rows if the value is "yes", but hide them if any other value.

**concurmgr** · 12-19-2012, 02:56 PM

Nevermind! I got it! Thank you so much for your help!!

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
If Not Intersect(Target, Range("A7")) Is Nothing Then
Rows("11:32").Hidden = LCase(Target) <> "yes"
End If
End Sub