Optimize if/elsif in VBA that hides / unhides rows based on cell value

bay32ft

New Member
Joined
Apr 4, 2017
Messages
18
I am very noobish in VBA and I would need some help in optimizing a piece of code.

Based on a cell value the code hide / unhide rows in another sheet.

As there are many things to hide / unhide my code is taking too long to run.

It looks like this :

Private Sub Worksheet_Change(ByVal Target As Range)

If Range("F46").Value = "Hide" Then
Sheets("Page 3").Rows("06:06").Hidden = True
ElseIf Range("F46").Value = "Show" Then
Sheets("Page 3").Rows("06:06").Hidden = False
End If

If Range("F47").Value = "Hide" Then
Sheets("Page 3").Rows("07:07").Hidden = True
ElseIf Range("F47").Value = "Show" Then
Sheets("Page 3").Rows("07:07").Hidden = False
End If

If Range("F48").Value = "Hide" Then
Sheets("Page 3").Rows("08:08").Hidden = True
ElseIf Range("F48").Value = "Show" Then
Sheets("Page 3").Rows("08:08").Hidden = False
End If

And continues for each cells.

There are 9 blocks of cells that hide / unhide rows based on values and for each of these'blocks' there is a link between the cell that hold the data and the rows that is hidden, let me explain :

In the code I posted you can see that, for exemple, when the cell 48 holds the value "Hide" the row 08 is hidden and for the cell 47 the row 07 is hidden. That's what I'm talking about when I write 'blocks'.

Please do not judge me too harsly, I am sure the answer is supper easy but I am not able to resovle my problem without a bit of help.
 

My Aswer Is This

Well-known Member
Joined
Jul 5, 2014
Messages
17,676
Office Version
  1. 2013
Platform
  1. Windows
Try this:
VBA Code:
Private Sub Worksheet_Change(ByVal Target As Range)
'Modified  5/10/2021  9:51:40 AM  EDT
If Target.Column = 6 And Target.Row > 45 Then
If Target.Cells.CountLarge > 1 Or IsEmpty(Target) Then Exit Sub
If Target.Value = "Hide" Then Sheets("Page 3").Rows(Target.Row).Hidden = True
If Target.Value = "Show" Then Sheets("Page 3").Rows(Target.Row).Hidden = False
End If
End Sub
 

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
62,537
Office Version
  1. 365
Platform
  1. Windows
That is hiding/unhiding the wrong rows. Did you actually look to see what the OP's code was doing?
 

bay32ft

New Member
Joined
Apr 4, 2017
Messages
18
Glad it's sorted & thanks for the feedback.
Sorry to come back but I would like to add something to this wonderful code that is :

If all the cells that have a drop down menu have the "Hide" option selected = some other cells are also hidden (Titles, if everything under the title is not shown I must hide also the title)

I tried to add it myself but I run into debugging errors or just non working code.

Thank you again for sharing your knowledge.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
62,537
Office Version
  1. 365
Platform
  1. Windows
What rows need to be hidden & what are the criteria?
 

bay32ft

New Member
Joined
Apr 4, 2017
Messages
18

ADVERTISEMENT

What rows need to be hidden & what are the criteria?
To sump up : IF F83 and F84 and F85 and F86 = « Hide » Then in sheet(« Page 3 ») the rows 52 and 53 will be hidden.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
62,537
Office Version
  1. 365
Platform
  1. Windows
Ok, you can do that like
VBA Code:
Private Sub Worksheet_Change(ByVal Target As Range)

   If Target.CountLarge > 1 Then Exit Sub
   If Not Intersect(Target, Range("F46:F51")) Is Nothing Then
      Sheets("Page 3").Rows(Target.Row - 40).Hidden = Target.Value = "Hide"
   ElseIf Not Intersect(Target, Range("F54:F58")) Is Nothing Then
      Sheets("Page 3").Rows(Target.Row - 38).Hidden = Target.Value = "Hide"
   ElseIf Not Intersect(Target, Range("F61:F71")) Is Nothing Then
      Sheets("Page 3").Rows(Target.Row - 36).Hidden = Target.Value = "Hide"
   ElseIf Not Intersect(Target, Range("F74:F76")) Is Nothing Then
      Sheets("Page 3").Rows(Target.Row - 34).Hidden = Target.Value = "Hide"
   ElseIf Not Intersect(Target, Range("F79:F81")) Is Nothing Then
      Sheets("Page 3").Rows(Target.Row - 32).Hidden = Target.Value = "Hide"
   ElseIf Not Intersect(Target, Range("F84:F87")) Is Nothing Then
      With Sheets("Page 3")
         .Rows(Target.Row - 29).Hidden = Target.Value = "Hide"
         .Rows("52:53").Hidden = Application.CountIf(Range("F84:F87"), "Hide") = 4
      End With
   End If
End Sub
 

My Aswer Is This

Well-known Member
Joined
Jul 5, 2014
Messages
17,676
Office Version
  1. 2013
Platform
  1. Windows

ADVERTISEMENT

Well in my original post I missed one step.
My code should have been:
VBA Code:
Private Sub Worksheet_Change(ByVal Target As Range)
'Modified  5/10/2021  12:13:46 PM  EDT
If Target.Column = 6 And Target.Row > 45 Then
If Target.Cells.CountLarge > 1 Or IsEmpty(Target) Then Exit Sub
If Target.Value = "Hide" Then Sheets("Page 3").Rows(Target.Row - 40).Hidden = True
If Target.Value = "Show" Then Sheets("Page 3").Rows(Target.Row - 40).Hidden = False
End If
End Sub


But now with these new request My code would be imcomplete
 

bay32ft

New Member
Joined
Apr 4, 2017
Messages
18
Ok, you can do that like
VBA Code:
Private Sub Worksheet_Change(ByVal Target As Range)

   If Target.CountLarge > 1 Then Exit Sub
   If Not Intersect(Target, Range("F46:F51")) Is Nothing Then
      Sheets("Page 3").Rows(Target.Row - 40).Hidden = Target.Value = "Hide"
   ElseIf Not Intersect(Target, Range("F54:F58")) Is Nothing Then
      Sheets("Page 3").Rows(Target.Row - 38).Hidden = Target.Value = "Hide"
   ElseIf Not Intersect(Target, Range("F61:F71")) Is Nothing Then
      Sheets("Page 3").Rows(Target.Row - 36).Hidden = Target.Value = "Hide"
   ElseIf Not Intersect(Target, Range("F74:F76")) Is Nothing Then
      Sheets("Page 3").Rows(Target.Row - 34).Hidden = Target.Value = "Hide"
   ElseIf Not Intersect(Target, Range("F79:F81")) Is Nothing Then
      Sheets("Page 3").Rows(Target.Row - 32).Hidden = Target.Value = "Hide"
   ElseIf Not Intersect(Target, Range("F84:F87")) Is Nothing Then
      With Sheets("Page 3")
         .Rows(Target.Row - 29).Hidden = Target.Value = "Hide"
         .Rows("52:53").Hidden = Application.CountIf(Range("F84:F87"), "Hide") = 4
      End With
   End If
End Sub
Sorry, it does not seem to work for me.
 

bay32ft

New Member
Joined
Apr 4, 2017
Messages
18
Ok, you can do that like
VBA Code:
Private Sub Worksheet_Change(ByVal Target As Range)

   If Target.CountLarge > 1 Then Exit Sub
   If Not Intersect(Target, Range("F46:F51")) Is Nothing Then
      Sheets("Page 3").Rows(Target.Row - 40).Hidden = Target.Value = "Hide"
   ElseIf Not Intersect(Target, Range("F54:F58")) Is Nothing Then
      Sheets("Page 3").Rows(Target.Row - 38).Hidden = Target.Value = "Hide"
   ElseIf Not Intersect(Target, Range("F61:F71")) Is Nothing Then
      Sheets("Page 3").Rows(Target.Row - 36).Hidden = Target.Value = "Hide"
   ElseIf Not Intersect(Target, Range("F74:F76")) Is Nothing Then
      Sheets("Page 3").Rows(Target.Row - 34).Hidden = Target.Value = "Hide"
   ElseIf Not Intersect(Target, Range("F79:F81")) Is Nothing Then
      Sheets("Page 3").Rows(Target.Row - 32).Hidden = Target.Value = "Hide"
   ElseIf Not Intersect(Target, Range("F84:F87")) Is Nothing Then
      With Sheets("Page 3")
         .Rows(Target.Row - 29).Hidden = Target.Value = "Hide"
         .Rows("52:53").Hidden = Application.CountIf(Range("F84:F87"), "Hide") = 4
      End With
   End If
End Sub
It works actually, I was just pretty dumnb, thank you again I have learned a lot !
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
62,537
Office Version
  1. 365
Platform
  1. Windows
Glad to help & thanks for the feedback.
 

Forum statistics

Threads
1,141,606
Messages
5,707,360
Members
421,503
Latest member
Rickys03

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