Use VBA to hide/unhide rows based on cell values

fhzhkunming

New Member
Joined
Mar 4, 2021
Messages
30
Office Version
  1. 365
Platform
  1. Windows
Hi Folks,

I have a simple situation but I couldn't make it working.
I want to hide Rows 29-31 if D28 = "Yes" (Fig.1), and want to hide only Row 31 if D28 ="No" and D29 = "Number" (Fig. 2). In other words, if D28 = "Yes", Rows 29-31 are empty (Actually contents in these rows were invisible by conditional formatting); if D28 = "No" and D29 = "Number", only Row 31 is empty row (contents invisible). BTW, values of D28 and D29 were automatically copied from another sheet. I want this hide/unhide event to be automatically triggered.

Below is my code. Could anyone please help me out?

Thanks in advance.

Frank


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

'MsgBox "Changed: " & Target.Address

    Application.EnableEvents = False 'pervent triggering another change event

If Not Intersect(Target, Range("D28")) Is Nothing Then
    If Range("D28").Value = "Yes" Then
                Range("D29:D31").EntireRow.Hidden = True
            ElseIf Range("D28").Value = "No" And Range("D29").Value = "Number" Then
                Range("D31").EntireRow.Hidden = True
            Else
                Range("D29:D31").EntireRow.Hidden = False
    
        End If

End If

Application.EnableEvents = True 're-enable events in the end

End Sub
 

Attachments

  • hide_rows-1.JPG
    hide_rows-1.JPG
    38.3 KB · Views: 12
  • hide_rows-2.JPG
    hide_rows-2.JPG
    46.1 KB · Views: 15

fhzhkunming

New Member
Joined
Mar 4, 2021
Messages
30
Office Version
  1. 365
Platform
  1. Windows
It works fine for me & there is nothing in the change I made that would affect changing G26 to Yes.
It's wired. I completely cleared out everything and copied your code. It worked fine except one step: if G30 = Budget, then Row 33 should be unhidden, but now it is hidden. So I added one more ElsoIf ...Then statement and it worked.
VBA Code:
Private Sub Worksheet_Change(ByVal Target As Range)

If Not Intersect(Target, Range("G26")) Is Nothing Then
    If Range("G26").Value = "Yes" Then
       Range("G30").Value = ""
       Range("G31:G32").ClearContents
       Sheets("Stage C-Step 11").Rows("31:33").EntireRow.Hidden = True
'       ElseIf Range("G26").Value = "No" Then
'            Range("G30").Value = "Choose answer"
       ElseIf Range("G26").Value = "No" And Range("G30").Value = "Number" Then
           Sheets("Stage C-Step 11").Rows("33").EntireRow.Hidden = True                   'This step is not working.
       Else
           Sheets("Stage C-Step 11").Rows("31:33").EntireRow.Hidden = False
    End If

End If

If Not Intersect(Target, Range("G30")) Is Nothing Then
     If Range("G26").Value = "No" And Range("G30").Value = "Number" Then
           Sheets("Stage C-Step 11").Rows("33").EntireRow.Hidden = True
     ElseIf Range("G26").Value = "No" And Range("G30").Value = "Budget" Then  'new 
           Sheets("Stage C-Step 11").Rows("33").EntireRow.Hidden = False               'new
     End If
    
     Range("G31:G32").ClearContents
End If

End Sub

I have a new idea. Since if G26 = Yes, no more action is needed for G30. So I wanted: if G26 = Yes, then G30 = "" (don't need to choose an option); if G26= No, then G30 = "Choose answer" (remind user to choose an option from dropdown). I added two lines (commented out in the code). But not working. Any idea?

Thank you.
 

Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
68,064
Office Version
  1. 365
Platform
  1. Windows
How about
VBA Code:
Private Sub Worksheet_Change(ByVal Target As Range)

If Not Intersect(Target, Range("G26")) Is Nothing Then
    If Range("G26").Value = "Yes" Then
       Range("G30").Value = ""
       Range("G31:G32").ClearContents
       Sheets("Stage C-Step 11").Rows("31:33").EntireRow.Hidden = True
       ElseIf Range("G26").Value = "No" And Range("G30").Value = "Number" Then
           Sheets("Stage C-Step 11").Rows("33").EntireRow.Hidden = True                   'This step is not working.
       ElseIf Range("G26").Value = "No" Then
            Application.EnableEvents = False
            Range("G30").Value = "Choose answer"
            Application.EnableEvents = True
       Else
           Sheets("Stage C-Step 11").Rows("31:33").EntireRow.Hidden = False
    End If

End If

If Not Intersect(Target, Range("G30")) Is Nothing Then
     If Range("G26").Value = "No" And Range("G30").Value = "Number" Then
           Sheets("Stage C-Step 11").Rows("33").EntireRow.Hidden = True
     ElseIf Range("G26").Value = "No" Then
           Sheets("Stage C-Step 11").Rows("33").EntireRow.Hidden = False               'new
     End If
    
     Range("G31:G32").ClearContents
End If

End Sub
 
Solution

fhzhkunming

New Member
Joined
Mar 4, 2021
Messages
30
Office Version
  1. 365
Platform
  1. Windows
How about
VBA Code:
Private Sub Worksheet_Change(ByVal Target As Range)

If Not Intersect(Target, Range("G26")) Is Nothing Then
    If Range("G26").Value = "Yes" Then
       Range("G30").Value = ""
       Range("G31:G32").ClearContents
       Sheets("Stage C-Step 11").Rows("31:33").EntireRow.Hidden = True
       ElseIf Range("G26").Value = "No" And Range("G30").Value = "Number" Then
           Sheets("Stage C-Step 11").Rows("33").EntireRow.Hidden = True                   'This step is not working.
       ElseIf Range("G26").Value = "No" Then
            Application.EnableEvents = False
            Range("G30").Value = "Choose answer"
            Application.EnableEvents = True
       Else
           Sheets("Stage C-Step 11").Rows("31:33").EntireRow.Hidden = False
    End If

End If

If Not Intersect(Target, Range("G30")) Is Nothing Then
     If Range("G26").Value = "No" And Range("G30").Value = "Number" Then
           Sheets("Stage C-Step 11").Rows("33").EntireRow.Hidden = True
     ElseIf Range("G26").Value = "No" Then
           Sheets("Stage C-Step 11").Rows("33").EntireRow.Hidden = False               'new
     End If
   
     Range("G31:G32").ClearContents
End If

End Sub

Thanks for the code, but once Rows 31:33 were hidden, they weren't unhidden at my end. That means, if G26 = No, G30 = Number, Rows 31:33 were hidden on sheet "Stage C-Step 11" and I wanted to hide only Row 33; same for G26 = No and G30 = Budget, and in this situation, no rows should be hidden. It seems the code of the second IF Not Intersect is not working properly.

Thanks
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
68,064
Office Version
  1. 365
Platform
  1. Windows
You never said they should be. ;)
Easiest option is to just unhide all the rows first & then hide which ever rows need to be hidden.
 

fhzhkunming

New Member
Joined
Mar 4, 2021
Messages
30
Office Version
  1. 365
Platform
  1. Windows
You never said they should be. ;)
Easiest option is to just unhide all the rows first & then hide which ever rows need to be hidden.
If I didn't state that clearly, I am sorry. But the purpose is to hide and unhide rows based on conditions or cells' values.
Thank you for the idea to unhide all rows at first. It turns out the following code works. Please let me know if you have better ideas.

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

If Not Intersect(Target, Range("G26")) Is Nothing Then
    Sheets("Stage C-Step 11").Rows("31:33").EntireRow.Hidden = False
    If Range("G26").Value = "Yes" Then
       Range("G30").Value = ""
       Range("G31:G32").ClearContents
       Sheets("Stage C-Step 11").Rows("31:33").EntireRow.Hidden = True
       ElseIf Range("G26").Value = "No" And Range("G30").Value = "Number" Then
           Sheets("Stage C-Step 11").Rows("33").EntireRow.Hidden = True                   
       ElseIf Range("G26").Value = "No" Then
            Application.EnableEvents = False
            Range("G30").Value = "Choose answer"
            Application.EnableEvents = True
       Else
           Sheets("Stage C-Step 11").Rows("31:33").EntireRow.Hidden = False
    End If

End If

If Not Intersect(Target, Range("G30")) Is Nothing Then
     If Range("G26").Value = "No" And Range("G30").Value = "Number" Then
           Sheets("Stage C-Step 11").Rows("33").EntireRow.Hidden = True
     ElseIf Range("G26").Value = "No" Then
           Sheets("Stage C-Step 11").Rows("33").EntireRow.Hidden = False               
     End If
    
     Range("G31:G32").ClearContents
End If

End Sub


Thank you very much for your time and help, Fluff.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
68,064
Office Version
  1. 365
Platform
  1. Windows
That looks fine. :)
Glad to help & thanks for the feedback.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,152,036
Messages
5,767,775
Members
425,431
Latest member
Sayson

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