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: 9
  • hide_rows-2.JPG
    hide_rows-2.JPG
    46.1 KB · Views: 12

fhzhkunming

New Member
Joined
Mar 4, 2021
Messages
30
Office Version
  1. 365
Platform
  1. Windows
The cause of that might be a premature interruption of your own code. Run the code below once and try again. Be sure to paste the code of my post #4 in de module of the sheet to be affected.
VBA Code:
Sub RunOnce()
    Application.EnableEvents = True
End Sub
Thanks for the suggestion. But it's still not working. I put a line "MsgBox "Changed: " & Target.Address" to show if the VBA is triggered, but it seems not triggered.
 

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college

GWteB

Well-known Member
Joined
Sep 10, 2010
Messages
1,827
Office Version
  1. 2013
Platform
  1. Windows
In that case the Change event is not suitable in your situation and the Calculation event should be used.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
62,319
Office Version
  1. 365
Platform
  1. Windows
Are the D28 & D29 cells on sheet you want to hide the rows, changed via a data validation drop-down?
 

fhzhkunming

New Member
Joined
Mar 4, 2021
Messages
30
Office Version
  1. 365
Platform
  1. Windows
Are the D28 & D29 cells on sheet you want to hide the rows, changed via a data validation drop-down?
Yes, D28 & D29 cells are on the sheet I want to hide the rows, but value changed via data validation drop-down in a previous sheet.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
62,319
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

If the drop down is on anther sheet, how are the cells being changed?
 

fhzhkunming

New Member
Joined
Mar 4, 2021
Messages
30
Office Version
  1. 365
Platform
  1. Windows
If the drop down is on anther sheet, how are the cells being changed?
I think I got your point. Once a value is selected from the drop down from another sheet, the value of D28 & 29 has been changed before the sheet I want to hide/unhide the rows is activated. So when the sheet is active, nothing change to trigger the code.
If that is the case, is it possible to trigger the code on the sheet containing the drop down, and make changes (hide/unhide rows) ton the inactive sheet?

Thanks
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
62,319
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Yes that's possible, what is the name of the sheet you want to hide the rows on?
 

fhzhkunming

New Member
Joined
Mar 4, 2021
Messages
30
Office Version
  1. 365
Platform
  1. Windows
It has been proved that the idea is feasible. The problem is solved. Here is my code:

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

If Not Intersect(Target, Range("G26")) Is Nothing Then Exit Sub
    If Range("G26").Value = "Yes" Then
            Sheets("Stage C-Step 11").Rows("29:31").EntireRow.Hidden = True
        ElseIf Range("G26").Value = "No" And Range("G30").Value = "Number" Then
            Sheets("Stage C-Step 11").Rows("31").EntireRow.Hidden = True
        Else
            Sheets("Stage C-Step 11").Rows("29:31").EntireRow.Hidden = False

    End If

  
End Sub

Thanks to those have provided help. Appreciate it your brilliant idea or code.

Have a wonderful weekend.

Frank
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
62,319
Office Version
  1. 365
Platform
  1. Windows
Glad you sorted it & thanks for the feedback.
 

fhzhkunming

New Member
Joined
Mar 4, 2021
Messages
30
Office Version
  1. 365
Platform
  1. Windows
@fhzhkunming, in your code any change of cell D29 isn't captured. How about:
VBA Code:
Private Sub Worksheet_Change(ByVal Target As Range)

    If Intersect(Target, Range("D28:D29")) Is Nothing Then Exit Sub

    If Range("D28").Value = "Yes" Then
        Rows("29:31").Hidden = True

    ElseIf Range("D28").Value = "No" And Range("D29").Value = "Number" Then
        Rows("31").Hidden = True

    Else
        Rows("29:31").Hidden = False

    End If

End Sub

Thanks for your code that brought me the inspiration to eventually solve the problem.

Best regards,

Frank
 

Forum statistics

Threads
1,141,204
Messages
5,704,944
Members
421,372
Latest member
Jamie11

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