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

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.
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
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
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
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
That looks fine. :)
Glad to help & thanks for the feedback.
 
Upvote 0
You must log in or register to reply here.

Similar threads

B
  • Solved
Another VBA question to hide/unhide rows based on cell value in another sheet
2
Replies
12
Views
384
kevin9999
K
M
  • Question
Hide Rows based on cell with drop down values
Replies
1
Views
385
kevin9999
K
F
  • Solved
If formula
Replies
2
Views
66
Fegal
F
J
  • Question
VBA Macro for hiding and unhiding rows is rearranging cells
2
Replies
14
Views
921
dmt32
D
PuntingJawa
  • Solved
Macro to hide/unhide rows based on cell value
2
Replies
11
Views
141
PuntingJawa
PuntingJawa

Forum statistics

Threads
1,214,591
Messages
6,120,432
Members
448,961
Latest member
nzskater

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
Back
Top