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
 

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.
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]
 
Upvote 0
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:
Upvote 0
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?
 
Upvote 0
Thanks for the reply Fluff. I think Joe was able to help me out. C8 is being changed manually only
 
Upvote 0
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:
Upvote 0
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:
Upvote 0
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:
Upvote 0
Glad we could help & thanks for the feedback
 
Upvote 0

Forum statistics

Threads
1,213,567
Messages
6,114,342
Members
448,570
Latest member
rik81h

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