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