Use VBA to hide/unhide rows based on cell values

F

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: 14
  • hide_rows-2.JPG
    hide_rows-2.JPG
    46.1 KB · Views: 17
Sort by date Sort by votes
F

fhzhkunming

New Member
Joined
Mar 4, 2021
Messages
30
Office Version
  1. 365
Platform
  1. Windows
Fluff said:
It works fine for me & there is nothing in the change I made that would affect changing G26 to Yes.
Click to expand...
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.
 
Upvote 0

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.
Fluff

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
85,157
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
 
Upvote 0
Solution
F

fhzhkunming

New Member
Joined
Mar 4, 2021
Messages
30
Office Version
  1. 365
Platform
  1. Windows
Fluff said:
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
Click to expand...

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
 
Upvote 0
Fluff

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
85,157
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.
 
Upvote 0
F

fhzhkunming

New Member
Joined
Mar 4, 2021
Messages
30
Office Version
  1. 365
Platform
  1. Windows
Fluff said:
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.
Click to expand...
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.
 
Upvote 0
Fluff

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
85,157
Office Version
  1. 365
Platform
  1. Windows
That looks fine. :)
Glad to help & thanks for the feedback.
 
Upvote 0
You must log in or register to reply here.

Similar threads

K
  • Question
Hide/Unhide Rows based on cell values
Replies
2
Views
887
Alex Blakenburg
A
W
  • Question
How to automatically hide/unhide columns based on criteria with VBA
Replies
5
Views
625
WentzelN94
W
D
  • Solved
Hiding Rows based on multiple cell inputs
Replies
9
Views
161
DavidWT87
D
T
  • Question
Hide and unhide rows based on empty cells
Replies
0
Views
284
The_Steward
T
R
  • Question
Automate hide-unhide button based on heading style
Replies
0
Views
186
rahildhody
R

Forum statistics

Threads
1,195,934
Messages
6,012,383
Members
441,693
Latest member
TraderRick

Share this page

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