Hide rows based on cell result

[Lostguy]

Hello!
I am converting a Word Doc to Excel. It has a whole page of
"notes" (i.e., add this paragraph if needed.)

There are 4 paragraphs.

I was just going to ask the user in F1: "Do you require
transportation?". In g1 would be a Y/N data validation dropdown list.

The big transportation paragraph would be in, say, a10. Now, if the
user does NOT require transportation, g1 would be N, and I would not
want to show row 10.

How do I hide row 10 if g1=N??

(The other paragraphs would be in a11 (with question in f2 and answer
in g2), a12 (q in f3, answer in g3), and a13 (q in f4, answer in g4.
These would also be being hiden/unhidden based on their answer in
their respective g cell.)

Thanks!

VR/

Lost

 

[Roger Govier]

Hi

Copy the following event code to the relevant sheet.
As you make changes in cells G1 to G4, the corresponding rows will either be
hidden or visible.
I have put some rem statements to show what should be altered if you change
/ amend the ranges concerned.

Private Sub Worksheet_Change(ByVal Target As Range)
Application.ScreenUpdating = False
Dim i As Long, myrange As Range
Set myrange = Range("G1:G4") '<== Change to suit
If Not Intersect(Target, myrange) Is Nothing Then
Rows("10:13").Hidden = True ' <Change to suit
For i = 1 To 4
If UCase(Cells(i, "G")) = "Y" Then
' because first row to be shown / hidden is 9 more than
' answer row number then add 9 to i. Change if required
Rows(i + 9).Hidden = False
End If
Next i
End If
Application.ScreenUpdating = True
End Sub

To use this
Copy Code above
Right click on Sheet tab
View code
Paste in the white pane that appears in the VB Editor
Alt+F11 to return to Excel

 

[Lostguy]

Roger,

Thanks! I will try that this morning!

I was wondering if there was a way to do it wihout VBA?

Many of the users freak out when that macro warning pops up ("may
contain viruses") when you open a workbook containing VBA, so the
formula route works better for me.

Maybe do something with autofilter? (I can put leading characters in
front or back of the paragraphs if they help the filtering.)

Sorry-I should have stated about the macro virus VBA squeamishness in
my original post.

Appreciate your help and time.

VR/

Lost

 

[Don Guillett]

You could use conditional formatting to change the font color to white (or
the color of your background)

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(e-mail address removed)
Roger,

Thanks! I will try that this morning!

I was wondering if there was a way to do it wihout VBA?

Many of the users freak out when that macro warning pops up ("may
contain viruses") when you open a workbook containing VBA, so the
formula route works better for me.

Maybe do something with autofilter? (I can put leading characters in
front or back of the paragraphs if they help the filtering.)

Sorry-I should have stated about the macro virus VBA squeamishness in
my original post.

Appreciate your help and time.

VR/

Lost