Hide Row based upon Cell Value Macro

LPalmer

New Member
Joined
Oct 8, 2019
Messages
12
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
Joined
Aug 1, 2002
Messages
51,843
Office Version
365
Platform
Windows
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
Joined
Jun 12, 2014
Messages
33,965
Office Version
365
Platform
Windows
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
Joined
Oct 8, 2019
Messages
12
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
Joined
Oct 8, 2019
Messages
12
Thanks for the reply Fluff. I think Joe was able to help me out. C8 is being changed manually only
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
33,965
Office Version
365
Platform
Windows
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
Joined
Oct 8, 2019
Messages
12
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
Joined
Jun 12, 2014
Messages
33,965
Office Version
365
Platform
Windows
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:

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
33,965
Office Version
365
Platform
Windows
Glad we could help & thanks for the feedback
 

Forum statistics

Threads
1,086,084
Messages
5,387,709
Members
402,075
Latest member
COwen

Some videos you may like

This Week's Hot Topics

Top