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

B

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
Click to expand...

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.
 
Sort by date Sort by votes
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
 
Upvote 0

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.
That is hiding/unhiding the wrong rows. Did you actually look to see what the OP's code was doing?
 
Upvote 0
Fluff said:
Glad it's sorted & thanks for the feedback.
Click to expand...
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.
 
Upvote 0
What rows need to be hidden & what are the criteria?
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
Fluff said:
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
Click to expand...
Sorry, it does not seem to work for me.
 
Upvote 0
Fluff said:
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
Click to expand...
It works actually, I was just pretty dumnb, thank you again I have learned a lot !
 
Upvote 0
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
394
kevin9999
K
M
  • Question
Need multiple buttons to hide/show rows on another tab
Replies
8
Views
381
skillilea
S
B
  • Solved
Worksheet Change - multiple Loops in one sub
2
Replies
13
Views
204
mumps
mumps
J
  • Question
VBA Macro for hiding and unhiding rows is rearranging cells
2
Replies
14
Views
921
dmt32
D
B
  • Locked
  • Question
Multiply VBA Macro for hiding and unhiding rows
Replies
4
Views
377
RoryA
RoryA

Forum statistics

Threads
1,214,645
Messages
6,120,711
Members
448,984
Latest member
foxpro

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