Need help with hiding/unhiding lines based on formula result

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 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!
 

Some videos you may like

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.

PeanutHead

New Member
Joined
Jan 29, 2021
Messages
9
Office Version
  1. 2016
Platform
  1. Windows
hide-show-rows-columns (Autosaved).xls
D
1
Automatic1
 

PeanutHead

New Member
Joined
Jan 29, 2021
Messages
9
Office Version
  1. 2016
Platform
  1. Windows
hide-show-rows-columns (Autosaved).xls
ABCD
1Hidden ColumnQuestionsDropdown Boxes
2
3Question 1yes
4TrueFurther response to question 1
5TrueFurther response to question 1
6TrueFurther response to question 1
7TrueFurther response to question 1
8
9Question 2yes
10TrueFurther response to question 2
11TrueFurther response to question 2
12TrueFurther response to question 2
13TrueFurther response to question 2
14TrueFurther response to question 2
15TrueAnother yes/no queryno
16FalseFurther response to the yes/no query
17FalseFurther response to the yes/no query
18FalseFurther response to the yes/no query
Automatic1
Cell Formulas
RangeFormula
A4:A7A4=IF($C$3="yes","True","False")
A10:A15A10=IF($C$9="yes","True","False")
A16:A18A16=IF($C$15="yes","True","False")
Cells with Data Validation
CellAllowCriteria
C3List=$O$16:$O$19
C9List=$O$16:$O$19
C15List=$O$16:$O$19
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,674
Office Version
  1. 365
Platform
  1. Windows
Hi & welcome to MrExcel.
If you are happy to change your formulae to

+Fluff 1.xlsm
ABC
1Hidden ColumnQuestionsDropdown Boxes
2
3Question 1yes
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 2no
10FALSEFurther response to question 2
11FALSEFurther response to question 2
12FALSEFurther response to question 2
13FALSEFurther response to question 2
14FALSEFurther response to question 2
15Another yes/no queryyes
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
RangeFormula
A4:A7A4=IF($C$3="yes","")
A10:A14A10=IF($C$9="yes","")
A16:A18A16=IF($C$15="yes","")


You can use
VBA Code:
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
 
Solution

PeanutHead

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

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

Watch MrExcel Video

Forum statistics

Threads
1,127,838
Messages
5,627,176
Members
416,227
Latest member
Abid Fattani

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