Hide Row based upon Cell Value Macro

LPalmer

New Member
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

MrExcel MVP, Junior Admin
Welcome to the Board!

Is that what you mean?
Code:
[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

MrExcel MVP, Moderator
Hi & welcome to MrExcel.
How about
Rich (BB code):
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?
 
Last edited:

LPalmer

New Member
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

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

Fluff

MrExcel MVP, Moderator
in that case you can limit the when the event triggers & add other cells like
Rich (BB code):
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
 
Last edited:

LPalmer

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

Code:
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!
 
Last edited by a moderator:

Fluff

MrExcel MVP, Moderator
It needs to be like
Rich (BB code):
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
 
Last edited:

Some videos you may like

This Week's Hot Topics

Top