Help with efficiently hiding/unhiding rows using VBA

PeanutHead

New Member
Joined
Jan 29, 2021
Messages
9
Office Version
  1. 2016
Platform
  1. Windows
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!
 

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce

PeanutHead

New Member
Joined
Jan 29, 2021
Messages
9
Office Version
  1. 2016
Platform
  1. Windows
Website Example.xlsm.xlsx
ABCDE
1Hidden ColumnQuestionsDropdown BoxesDropdowns
2Yes
3Question 1YesNo
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
9Question 2Yes
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 queryNo
16FALSEFurther response to the yes/no query
17FALSEFurther response to the yes/no query
18FALSEFurther response to the yes/no query
Sheet1
Cell Formulas
RangeFormula
A4:A7A4=IF($C$3="yes","")
A10A10=IF($C$9="yes","","False")
A11:A15A11=IF($C$9="yes","")
A16:A18A16=IF($C$15="yes","")
Cells with Data Validation
CellAllowCriteria
C3List=$E$2:$E$3
C9List=$E$2:$E$3
C15List=$E$2:$E$3
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,842
Office Version
  1. 365
Platform
  1. Windows
You will need to change the formula in A10 to match the other formulae & then you can use
VBA Code:
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
 
Solution

PeanutHead

New Member
Joined
Jan 29, 2021
Messages
9
Office Version
  1. 2016
Platform
  1. Windows
Well, would you look at that! Quick and efficient. Very snazzy! Thank you so much - have a great weekend :)
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,842
Office Version
  1. 365
Platform
  1. Windows
You're welcome & thanks for the feedback.
 

Watch MrExcel Video

Forum statistics

Threads
1,130,104
Messages
5,640,128
Members
417,126
Latest member
Jeffman52

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top