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.
 

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
62,545
Office Version
  1. 365
Platform
  1. Windows
How are the values in col F being changed manually or via a formula?
 

bay32ft

New Member
Joined
Apr 4, 2017
Messages
18
How are the values in col F being changed manually or via a formula?
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
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
62,545
Office Version
  1. 365
Platform
  1. Windows
When you say drop down menu are you referring to a data validation drop down?
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
62,545
Office Version
  1. 365
Platform
  1. Windows
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
 
Solution

bay32ft

New Member
Joined
Apr 4, 2017
Messages
18

ADVERTISEMENT

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
WOW that works, can you explain to me how do you manage the offset ?
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
62,545
Office Version
  1. 365
Platform
  1. Windows
In what way?
 

Fluff

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

Forum statistics

Threads
1,141,618
Messages
5,707,427
Members
421,509
Latest member
someinternetuser

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