Need help with hiding/unhiding lines based on formula result

[PeanutHead]

Hi there! I hope someone out there can help me. I'm extremely new to VBA.

I have a sheet which has a number of yes/no dropdown boxes. Based on the results of these boxes, I'd like to hide/unhide rows. However, as the spreadsheet will have new rows added to it over time, I don't want to use exact cell references.

Instead, I have added in a hidden column containing formulae for each row based on the results of the dropdown boxes - producing a TRUE or FALSE. How can I use VBA to hide any rows containing FALSE in the hidden column? And MOST IMPORTANTLY, (because this is the thing I REALLY cannot figure out), how do I UNHIDE them if the formula changes to TRUE?

(To give you an idea of how the sheet works, if it helps to visualise it: It's a questionnaire, and each time you choose the dropdown to answer "yes", it reveals more rows with further questions. But we also want the option to go back and say "no", or change our answer and unhide/hide those sections again).

This is the code I've been working with. I can hide, but not unhide my rows!

Oh, and another added layer of complication - the spreadsheet is password protected. I've not even managed to think how that works, so any help would be *chef's kiss*

Private Sub Worksheet_Calculate()
Dim LastRow As Long, c As Range
Application.EnableEvents = False
LastRow = Cells(Cells.Rows.Count, "a").End(xlUp).Row
On Error Resume Next
For Each c In Range("a1:a" & LastRow)
If c.Value = "FALSE" Then
c.EntireRow.Hidden = True
Else
If c.Value = "TRUE" Then
c.EntireRow.Hidden = False
End If
On Error GoTo 0
Application.EnableEvents = True
End If
Next
End Sub

Any help you guys have would be madly appreciated! Thank you again - I've learnt a lot from lurking in this community!

 

[PeanutHead]

hide-show-rows-columns (Autosaved).xls
	D
1
Automatic1

 

[PeanutHead]

hide-show-rows-columns (Autosaved).xls
	A	B	C	D
1	Hidden Column	Questions	Dropdown Boxes
2
3		Question 1	yes
4	True	Further response to question 1
5	True	Further response to question 1
6	True	Further response to question 1
7	True	Further response to question 1
8
9		Question 2	yes
10	True	Further response to question 2
11	True	Further response to question 2
12	True	Further response to question 2
13	True	Further response to question 2
14	True	Further response to question 2
15	True	Another yes/no query	no
16	False	Further response to the yes/no query
17	False	Further response to the yes/no query
18	False	Further response to the yes/no query
Automatic1

Cell Formulas
Range	Formula
A4:A7	A4	=IF($C$3="yes","True","False")
A10:A15	A10	=IF($C$9="yes","True","False")
A16:A18	A16	=IF($C$15="yes","True","False")

Cells with Data Validation
Cell	Allow	Criteria
C3	List	=$O$16:$O$19
C9	List	=$O$16:$O$19
C15	List	=$O$16:$O$19

 

[Fluff]

Hi & welcome to MrExcel.
If you are happy to change your formulae to

+Fluff 1.xlsm
	A	B	C
1	Hidden Column	Questions	Dropdown Boxes
2
3		Question 1	yes
4		Further response to question 1
5		Further response to question 1
6		Further response to question 1
7		Further response to question 1
8
9		Question 2	no
10	FALSE	Further response to question 2
11	FALSE	Further response to question 2
12	FALSE	Further response to question 2
13	FALSE	Further response to question 2
14	FALSE	Further response to question 2
15		Another yes/no query	yes
16		Further response to the yes/no query
17		Further response to the yes/no query
18		Further response to the yes/no query
19
Data

Cell Formulas
Range	Formula
A4:A7	A4	=IF($C$3="yes","")
A10:A14	A10	=IF($C$9="yes","")
A16:A18	A16	=IF($C$15="yes","")

You can use

Private Sub Worksheet_Calculate()
   Rows.Hidden = False
   On Error Resume Next
   Range("A:A").SpecialCells(xlFormulas, xlLogical).EntireRow.Hidden = True
   On Error GoTo 0
End Sub

 

[PeanutHead]

Well, would you look at that! Problem solved! Thank you so much. Now I just need to figure out how to do it on a password-protected sheet, but I'll work on that and come back with a new thread if I need more support. Thanks for your time and advice

 

[Fluff]

You're welcome & thanks for the feedback.