Hide/Unhide Rows based on Cell Value

tlc53

Active Member
Joined
Jul 26, 2018
Messages
385
Hi there,

I have a list of questions which are answered by Yes/No Option buttons. The option buttons are linked to the neighbouring cell and return value 1 if Yes, and 2 if No.

So the user can add a comment, I would like if No is selected (cell value is 2), then the row directly under it is unhidden. If Yes is selected (cell value is 1) or neither option button are selected yet (cell value 0) then the row directly under remains hidden.

I'm a little stumped as to how to set this VBA code out. Here is my not-so-good attempt but hopefully it shows what I am trying to do. The result to the questions go from "I14:I62" so tying to do it this way, I would need to repeat the code many more times still.

VBA Code:
Private Sub Worksheet_Change(ByVal Target As Range)

If Range("I14").Value = 2 Then
    Range("I15").EntireRow.Hidden = False
Else
    Range("I15").EntireRow.Hidden = True

End If

If Range("I16").Value = 2 Then
    Range("I17").EntireRow.Hidden = False
Else
    Range("I17").EntireRow.Hidden = True

End If

If Range("I18").Value = 2 Then
    Range("I19").EntireRow.Hidden = False
Else
    Range("I19").EntireRow.Hidden = True

End Sub

Any help with this would be much appreciated.

Thanks!
 

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
13,538
Office Version
  1. 365
Platform
  1. Windows
How about something like this (untested).
VBA Code:
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("I14:I62")) Is Nothing Then
    If Target.Row Mod 2 = 0 Then
        Target.Offset(1).EntireRow.Hidden = Not (Target.Value = 2)
    End If
End If
End Sub
 

tlc53

Active Member
Joined
Jul 26, 2018
Messages
385
How about something like this (untested).
Thank you! That looks like the sort of code I'm after. However, for some reason, it isn't working/doing anything :unsure:
And I've just worked out why. It's because the number 0, 1 and 2 are not manually entered, but entered automatically via the option button.
Your code works perfectly if I manually enter the number in I14 for example.
Is there a way for it to work off formula driven/the option button answers??
 

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
13,538
Office Version
  1. 365
Platform
  1. Windows
At a glance it would appear that it doesn't work that way, I thought that it should work as the option button enters a value directly into the cell but have not had cause to try it until now.

Do you have any formulas in your sheet that would be affected by the option button change?

The only way that I have found to force the code to run is by a formula recalculating. The option button alone is not enough to trigger anything that I have tried so far.

A simple formula such as =COUNT(I14:I62) would be enough to trigger the code to run if you have somewhere that you can add that to the sheet.
VBA Code:
Private Sub Worksheet_calculate()
Dim c As Range
Application.ScreenUpdating = False
Application.Calculation = xlManual
For Each c In Range("I14:I62")
    If c.Row Mod 2 = 0 Then
        c.Offset(1).EntireRow.Hidden = Not (c.Value = 2)
    End If
Next
Application.Calculation = xlAutomatic
Application.ScreenUpdating = True
End Sub
 

tlc53

Active Member
Joined
Jul 26, 2018
Messages
385

ADVERTISEMENT

At a glance it would appear that it doesn't work that way, I thought that it should work as the option button enters a value directly into the cell but have not had cause to try it until now.

Do you have any formulas in your sheet that would be affected by the option button change?

The only way that I have found to force the code to run is by a formula recalculating. The option button alone is not enough to trigger anything that I have tried so far.

A simple formula such as =COUNT(I14:I62) would be enough to trigger the code to run if you have somewhere that you can add that to the sheet.
VBA Code:
[QUOTE="jasonb75, post: 5723408, member: 108602"]
At a glance it would appear that it doesn't work that way, I thought that it should work as the option button enters a value directly into the cell but have not had cause to try it until now.

Do you have any formulas in your sheet that would be affected by the option button change?

The only way that I have found to force the code to run is by a formula recalculating. The option button alone is not enough to trigger anything that I have tried so far.

A simple formula such as =COUNT(I14:I62) would be enough to trigger the code to run if you have somewhere that you can add that to the sheet.
[CODE=vba]
Private Sub Worksheet_calculate()
Dim c As Range
Application.ScreenUpdating = False
Application.Calculation = xlManual
For Each c In Range("I14:I62")
    If c.Row Mod 2 = 0 Then
        c.Offset(1).EntireRow.Hidden = Not (c.Value = 2)
    End If
Next
Application.Calculation = xlAutomatic
Application.ScreenUpdating = True
End Sub

[/QUOTE]
No, I have no other formulas in my sheet which are affected.
That new VBA code and the Count formula did the trick, thank you. It does run and work as I would want it to. The only thing is, it's slow to process. Often when the user is completing the form, they might put Yes for all the questions and so no comments/rows unhidden are needed. As it needs to calculate each question, it would take quite some time to complete.
If it only ran if No (value 2) came up, that could work. Otherwise, I may need to rethink how this form is laid out.
 

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
13,538
Office Version
  1. 365
Platform
  1. Windows
Unfortunately because it needs to run on calculation there is no way to link it to the change of a specific cell, it has to go through all of them each time which is why it is slower.

Does it need to hide the rows after each change or can it all just be done at the end? Maybe a 'submit' button to run the code once the user has finished rather than automating it?

I'm going to be offline for a few hours but will try and check back before the end of the day with updated code if any is required.
 

tlc53

Active Member
Joined
Jul 26, 2018
Messages
385

ADVERTISEMENT

Unfortunately because it needs to run on calculation there is no way to link it to the change of a specific cell, it has to go through all of them each time which is why it is slower.

Does it need to hide the rows after each change or can it all just be done at the end? Maybe a 'submit' button to run the code once the user has finished rather than automating it?

I'm going to be offline for a few hours but will try and check back before the end of the day with updated code if any is required.
Unfortunately, the rows need to be unhidden as each question is answered, so they can add a comment/note regarding.
It needs to function quickly, so I thought I'd try a Drop Down List instead. It all works okay however, I would prefer for it to be faster. There is still a lag when each question (yes/no) is answered. Is there a better way to be doing this that you can think of?
Here's my current VBA code. Not very sophisticated but it functions :)

VBA Code:
Private Sub Worksheet_Change(ByVal Target As Range)

    If Range("G14") = "No" Then
        Range("15:15").EntireRow.Hidden = False
    Else
        Range("15:15").EntireRow.Hidden = True
    End If

    If Range("G16") = "No" Then
        Range("17:17").EntireRow.Hidden = False
    Else
        Range("17:17").EntireRow.Hidden = True
    End If
    
    If Range("G18") = "No" Then
        Range("19:19").EntireRow.Hidden = False
    Else
        Range("19:19").EntireRow.Hidden = True
    End If
    
    If Range("G21") = "No" Then
        Range("22:22").EntireRow.Hidden = False
    Else
        Range("22:22").EntireRow.Hidden = True
    End If
        
    If Range("G23") = "No" Then
        Range("24:24").EntireRow.Hidden = False
    Else
        Range("24:24").EntireRow.Hidden = True
    End If
            
    If Range("G25") = "No" Then
        Range("26:26").EntireRow.Hidden = False
    Else
        Range("26:26").EntireRow.Hidden = True
    End If
        
    If Range("G27") = "No" Then
        Range("28:28").EntireRow.Hidden = False
    Else
        Range("28:28").EntireRow.Hidden = True
    End If
        
    If Range("G29") = "No" Then
        Range("30:30").EntireRow.Hidden = False
    Else
        Range("30:30").EntireRow.Hidden = True
    End If
        
    If Range("G31") = "No" Then
        Range("32:32").EntireRow.Hidden = False
    Else
        Range("32:32").EntireRow.Hidden = True
    End If
        
    If Range("G33") = "No" Then
        Range("34:34").EntireRow.Hidden = False
    Else
        Range("34:34").EntireRow.Hidden = True
    End If
        
    If Range("G35") = "No" Then
        Range("36:36").EntireRow.Hidden = False
    Else
        Range("36:36").EntireRow.Hidden = True
    End If
       
    If Range("G41") = "No" Then
        Range("42:42").EntireRow.Hidden = False
    Else
        Range("42:42").EntireRow.Hidden = True
    End If
        
    If Range("G43") = "No" Then
        Range("44:44").EntireRow.Hidden = False
    Else
        Range("44:44").EntireRow.Hidden = True
    End If
        
    If Range("G45") = "No" Then
        Range("46:46").EntireRow.Hidden = False
    Else
        Range("46:46").EntireRow.Hidden = True
    End If
        
    If Range("G47") = "No" Then
        Range("48:48").EntireRow.Hidden = False
    Else
        Range("48:48").EntireRow.Hidden = True
    End If
        
    If Range("G49") = "No" Then
        Range("50:50").EntireRow.Hidden = False
    Else
        Range("50:50").EntireRow.Hidden = True
    End If
        
    If Range("G51") = "No" Then
        Range("52:52").EntireRow.Hidden = False
    Else
        Range("52:52").EntireRow.Hidden = True
    End If
        
    If Range("G54") = "No" Then
        Range("55:55").EntireRow.Hidden = False
    Else
        Range("55:55").EntireRow.Hidden = True
    End If
        
    If Range("G56") = "No" Then
        Range("57:57").EntireRow.Hidden = False
    Else
        Range("57:57").EntireRow.Hidden = True
    End If
        
    If Range("G58") = "No" Then
        Range("59:59").EntireRow.Hidden = False
    Else
        Range("59:59").EntireRow.Hidden = True
    End If
        
    If Range("G60") = "No" Then
        Range("61:61").EntireRow.Hidden = False
    Else
        Range("61:61").EntireRow.Hidden = True
    End If
        
    If Range("G62") = "No" Then
        Range("63:63").EntireRow.Hidden = False
    Else
        Range("63:63").EntireRow.Hidden = True
    End If


End Sub
 

GWteB

Well-known Member
Joined
Sep 10, 2010
Messages
1,830
Office Version
  1. 2013
Platform
  1. Windows
@tlc53, a minor tweak to @jasonb75's post #2 code might look like this, and is likely to run faster than the code of your previous post.
VBA Code:
Private Sub Worksheet_Change(ByVal Target As Range)
    Dim c As Range
    If Not Intersect(Target, Range("G14:G62")) Is Nothing Then
        For Each c In Target
            If c.Row Mod 2 = 0 Then
                c.Offset(1).EntireRow.Hidden = Not (StrComp(c.Value, "No", vbTextCompare) = 0)
            End If
        Next c
    End If
End Sub
 
Solution

GWteB

Well-known Member
Joined
Sep 10, 2010
Messages
1,830
Office Version
  1. 2013
Platform
  1. Windows
You are welcome, glad we could help.
 

Forum statistics

Threads
1,141,299
Messages
5,705,571
Members
421,399
Latest member
hjweiss00

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