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.
 

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.
Sort by date Sort by votes
How are the values in col F being changed manually or via a formula?
 
Upvote 0
Fluff said:
How are the values in col F being changed manually or via a formula?
Click to expand...
Via a drop down menu, I will past my horrible code in full :

VBA Code: 
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

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


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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

    If Range("F81").Value = "Hide" Then
        Sheets("Page 3").Rows("49:49").Hidden = True
    ElseIf Range("F81").Value = "Show" Then
        Sheets("Page 3").Rows("49:49").Hidden = False
        End If
       
    If Range("F84").Value = "Hide" Then
        Sheets("Page 3").Rows("55:55").Hidden = True
    ElseIf Range("F84").Value = "Show" Then
        Sheets("Page 3").Rows("55:55").Hidden = False
        End If
       
    If Range("F85").Value = "Hide" Then
        Sheets("Page 3").Rows("56:56").Hidden = True
    ElseIf Range("F85").Value = "Show" Then
        Sheets("Page 3").Rows("56:56").Hidden = False
        End If
       
    If Range("F86").Value = "Hide" Then
        Sheets("Page 3").Rows("57:57").Hidden = True
    ElseIf Range("F86").Value = "Show" Then
        Sheets("Page 3").Rows("57:57").Hidden = False
        End If
       
            If Range("F87").Value = "Hide" Then
        Sheets("Page 3").Rows("58:58").Hidden = True
    ElseIf Range("F87").Value = "Show" Then
        Sheets("Page 3").Rows("58:58").Hidden = False
        End If
       
       
End Sub
 
Upvote 0
When you say drop down menu are you referring to a data validation drop down?
 
Upvote 0
In that case, how about
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
      Sheets("Page 3").Rows(Target.Row - 29).Hidden = Target.Value = "Hide"
   End If
End Sub
 
Upvote 0
Solution
Fluff said:
In that case, how about
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
      Sheets("Page 3").Rows(Target.Row - 29).Hidden = Target.Value = "Hide"
   End If
End Sub
Click to expand...
WOW that works, can you explain to me how do you manage the offset ?
 
Upvote 0
Glad it's sorted & thanks for the feedback.
 
Upvote 0
You must log in or register to reply here.

Similar threads

N
  • Question
Hiding rows based on cell value
Replies
2
Views
77
smozgur
smozgur
B
  • Solved
Another VBA question to hide/unhide rows based on cell value in another sheet
2
Replies
12
Views
395
kevin9999
K
M
  • Question
Need multiple buttons to hide/show rows on another tab
Replies
8
Views
383
skillilea
S
PuntingJawa
  • Solved
Macro to hide/unhide rows based on cell value
2
Replies
11
Views
147
PuntingJawa
PuntingJawa
B
  • Solved
Worksheet Change - multiple Loops in one sub
2
Replies
13
Views
205
mumps
mumps

Forum statistics

Threads
1,214,652
Messages
6,120,746
Members
448,989
Latest member
mariah3

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