Hide Row based upon Cell Value Macro

[LPalmer]

Hello All,

I have a macro below that works fine but I would like the rows to be hidden if the cell value is blank as well. Any help appreciated

Private Sub Worksheet_Change(ByVal Target As Range)

'Message Box Appears or Unhides Rows if Value is less than 25
If Range("C8").Value < 25 Then
Rows("10:11").EntireRow.Hidden = False
MsgBox "Unless the plan has less than 250 participants, it would be unusual to have a sample size of less than 25. Verify that this is the sample size that was determined to be appropriate."

'Hides Rows if Value is equal to or greater than 25
ElseIf Range("C8").Value >= 25 Then
Rows("10:11").EntireRow.Hidden = True


End If
End Sub

 

[Joe4]

Welcome to the Board!

Is that what you mean?

[COLOR=#333333]Private Sub Worksheet_Change(ByVal Target As Range)[/COLOR]

[COLOR=#333333]'Message Box Appears or Unhides Rows if Value is less than 25[/COLOR]
[COLOR=#ff0000]If (Range("C8").Value < 25) And (Range("C8").Value <> "") Then[/COLOR]
[COLOR=#333333]Rows("10:11").EntireRow.Hidden = False[/COLOR]
[COLOR=#333333]MsgBox "Unless the plan has less than 250 participants, it would be unusual to have a sample size of less than 25. Verify that this is the sample size that was determined to be appropriate."[/COLOR]

[COLOR=#333333]'Hides Rows if Value is equal to or greater than 25[/COLOR]
[COLOR=#ff0000]ElseIf (Range("C8").Value >= 25) Or (Range("C8").Value = "") Then[/COLOR]
[COLOR=#333333]Rows("10:11").EntireRow.Hidden = True[/COLOR]

[COLOR=#333333]End If[/COLOR]
[COLOR=#333333]End Sub[/COLOR]

 

[Fluff]

Hi & welcome to MrExcel.
How about

Private Sub Worksheet_Change(ByVal Target As Range)

   'Message Box Appears or Unhides Rows if Value is less than 25
   If Range("C8").Value < 25  Then
      Rows("10:11").EntireRow.Hidden = False
      MsgBox "Unless the plan has less than 250 participants, it would be unusual to have a sample size of less than 25. Verify that this is the sample size that was determined to be appropriate."
      
      'Hides Rows if Value is equal to or greater than 25
      ElseIf Range("C8").Value >= 25 Or Range("C8").Value = "" Then
         Rows("10:11").EntireRow.Hidden = True
      
      
   End If
End Sub

Is C8 being changed manually or by a formula?

 

[LPalmer]

Thanks Joe! If I wanted to add other lines based upon different cell values in the same worksheet is it better to start an entirely new macro or could I add it to the existing one? Also, I notice that the message box appears everytime I hit enter in any worksheet cell, anyway to make the message box appear only when the value changes in Cell C8?

 

[LPalmer]

Thanks for the reply Fluff. I think Joe was able to help me out. C8 is being changed manually only

 

[Fluff]

in that case you can limit the when the event triggers & add other cells like

Private Sub Worksheet_Change(ByVal Target As Range)
   If Target.CountLarge > 1 Then Exit Sub
   If Target.Address(0, 0) = "C8" Then
      'Message Box Appears or Unhides Rows if Value is less than 25
      If Target < 25 And Target <> "" Then
         Rows("10:11").EntireRow.Hidden = False
         MsgBox "Unless the plan has less than 250 participants, it would be unusual to have a sample size of less than 25. Verify that this is the sample size that was determined to be appropriate."
         
      'Hides Rows if Value is equal to or greater than 25
      ElseIf Target >= 25 Or Target = "" Then
         Rows("10:11").EntireRow.Hidden = True
      
      End If
   ElseIf Target.Address(0, 0) = "D8" Then
      'do something
   End If
End Sub

 

[LPalmer]

Thanks Fluff. I updated Macro to the following but am now getting a compile error: Block If without End if.

Private Sub Worksheet_Change(ByVal Target As Range)
   If Target.CountLarge > 1 Then Exit Sub
   
   If Target.Address(0, 0) = "C8" Then
      'Message Box Appears or Unhides Rows if Value is less than 25
      If Target < 25 And Target <> "" Then
         Rows("10:11").EntireRow.Hidden = False
         MsgBox "Unless the plan has less than 250 participants, it would be unusual to have a sample size of less than 25. Verify that this is the sample size that was determined to be appropriate."
         
      'Hides Rows if Value is equal to or greater than 25
      ElseIf Target >= 25 Or Target = "" Then
         Rows("10:11").EntireRow.Hidden = True
      
      End If
      
      If Target.Address(0, 0) = "C12" Then
      'Hides Rows if Cell C12 equals "No"
      If Target = "No" Then
         Rows("13:15").EntireRow.Hidden = True
      
   End If
End Sub

Appreciate the help!

 

[Fluff]

It needs to be like

Private Sub Worksheet_Change(ByVal Target As Range)
   If Target.CountLarge > 1 Then Exit Sub
   
   If Target.Address(0, 0) = "C8" Then
      'Message Box Appears or Unhides Rows if Value is less than 25
      If Target < 25 And Target <> "" Then
         Rows("10:11").EntireRow.Hidden = False
         MsgBox "Unless the plan has less than 250 participants, it would be unusual to have a sample size of less than 25. Verify that this is the sample size that was determined to be appropriate."
         
         'Hides Rows if Value is equal to or greater than 25
      ElseIf Target >= 25 Or Target = "" Then
         Rows("10:11").EntireRow.Hidden = True
      
      End If
      
   ElseIf Target.Address(0, 0) = "C12" Then
      'Hides Rows if Cell C12 equals "No"
      If Target = "No" Then
         Rows("13:15").EntireRow.Hidden = True
      End If
   End If
End Sub

Also when posting code please use code tags, the # icon in the reply window. It maintains formatting & makes it easier to read

 

[LPalmer]

Got it. Thank you for your help!

 

[Fluff]

Glad we could help & thanks for the feedback