Help with efficiently hiding/unhiding rows using VBA

[PeanutHead]

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

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

I have added in a hidden column containing formulae for each row based on the results of the dropdown boxes - producing FALSE. Someone here helped me use VBA to create a module hide any rows containing FALSE in the hidden column. Then you use a button to run the module, revealing and hiding rows. Unfortunately, the sheet is now 600 rows long and takes forever to update whenever the module is run.

(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 VBA module I've been working with, and an example of the sheet I've been working with. It works really well if there aren't 600 rows (and growing), so any advice on making it more efficient/faster would be great.

Private Sub Worksheet_Change(ByVal Target As Range)
'Project Management to Partners and Stakeholders
Sub HideOrShowRows()
Dim A As Range
Set A = Range("A1:A600")
A.EntireRow.Hidden = False
For I = 1 To 600
If Cells(I, 1).Value <> "" And Cells(I, 1).Value = False Then
Cells(I, 1).EntireRow.Hidden = True
End If
Next I
End Sub

I will try and remember how to post the sheet in a second!

 

[PeanutHead]

Website Example.xlsm.xlsx
	A	B	C	D	E
1	Hidden Column	Questions	Dropdown Boxes		Dropdowns
2					Yes
3		Question 1	Yes		No
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	Yes
10		Further response to question 2
11		Further response to question 2
12		Further response to question 2
13		Further response to question 2
14		Further response to question 2
15		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
Sheet1

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

Cells with Data Validation
Cell	Allow	Criteria
C3	List	=$E$2:$E$3
C9	List	=$E$2:$E$3
C15	List	=$E$2:$E$3

 

[Fluff]

You will need to change the formula in A10 to match the other formulae & then you can use

Sub PeanutHead()
   Range("A1:A600").EntireRow.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! Quick and efficient. Very snazzy! Thank you so much - have a great weekend

 

[Fluff]

You're welcome & thanks for the feedback.