Hiding Rows based on user input

[coake]

Hey Folks,

By default I display 100 rows (rows 21 to 120)
In cell I19 I ask, how rows do you want to see? Lets say the answer is 10, but it could be later changed to 15, or 3.
Based on that, what would the syntax be to hide rows 31 to 100 and have the hidden rows hide/unhide based on the changing value in I19?

I have seen some pretty fancy examples to hide rows based on values in the actual row, or hiding a range of rows based on certain criteria, but nothing as simple as I'm looking to do.

Obviously, this does not work

Application.ScreenUpdating = False
On Error Resume Next
Dim NumRows As Integer

NumRows = "I19" + 2
Worksheets("Charter").Range("NumRows:$120").EntireRow.Hidden = True

End Sub


Regards,

 

[Rick Rothstein]

Assuming the value in cell I19 is changed manually (that is, the cell does not contain a formula), then this event code should do what you want automatically...

Private Sub Worksheet_Change(ByVal Target As Range)
  If Target.Address(0, 0) = "I19" Then
    If Target.Value < 1 Or Target.Value > 100 Then
      MsgBox "Bad number of rows specified!", vbCritical
    Else
      Rows("21:120").Hidden = True
      Rows(21).Resize(Range("I19").Value).Hidden = False
    End If
  End If
End Sub

HOW TO INSTALL Event Code
------------------------------------
If you are new to event code procedures, they are easy to install. To install it, right-click the name tab at the bottom of the worksheet that is to have the functionality to be provided by the event code and select "View Code" from the popup menu that appears. This will open up the code window for that worksheet. Copy/Paste the event code into that code window. That's it... the code will now operate automatically when its particular event procedure is raised by an action you take on the worksheet itself. Note... if you are using XL2007 or above, make sure you save your file as an "Excel Macro-Enabled Workbook (*.xlsm) and answer the "do you want to enable macros" question as "yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.

 

[coake]

Thanks Rick, that worked very well!

I see, you hid all the rows required, then do not hide the rows from 21 to the range defined by "cell value". Thank you!

Also,
If I wanted to do it in a different area on the same sheet, how can I go about that?
For example, if I wanted to repeat the process from lines 49 to 98 based on the value (not a formula) in J47 and assuming these are not hidden by the previous code (I changed the previous code to only hide 25 lines)?

 

[coake]

I just add another If statement
So now the code looks like this:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address(0, 0) = "I19" Then
If Target.Value < 1 Or Target.Value > 100 Then
MsgBox "Please enter a value between 1 and 100.", vbCritical
Else
Rows("21:45").Hidden = True
Rows(21).Resize(Range("I19").Value).Hidden = False
End If
End If
If Target.Address(0, 0) = "J47" Then
If Target.Value < 1 Or Target.Value > 30 Then
MsgBox "Please enter a value between 1 and 30.", vbCritical
Else
Rows("49:78").Hidden = True
Rows(49).Resize(Range("J47").Value).Hidden = False
End If
End If
End Sub

 

[coake]

On to the next section of hiding rows

Anyone know how I accomplish this effectively?
Basically I have an Active X Checkbox Named EPR (plus many more)
All rows (55:90) are hidden on Activate.
What I would like to do is, UNHIDE the corresponding row when the checkbox is clicked.

The .Visible doesn't work when I tried this..

Private Sub EPR_Click()
Worksheets("HSE Req").Range("$55:$55").EntireRow.Visible = EPR.Value
End Sub

This does work, but I'm looking for something more efficient

Private Sub EPR_Click()
Application.ScreenUpdating = False
If EPR.Value = True Then
Worksheets("HSE_Req").Range("$55:$55").EntireRow.Hidden = False
ElseIf EPR = False Then
Worksheets("HSE_Req").Range("$55:$55").EntireRow.Hidden = True
End If
Application.ScreenUpdating = True
End Sub



Any thoughts on a more efficient way to accomplish this?

 

[Rick Rothstein]

Give this a try (note that you do not need to deactivate screen updating for a single line of code)...

[table="width: 500"]
[tr]
	[td]Private Sub EPR_Click()
  Worksheets("HSE_Req").Range("$55:$55").EntireRow.Hidden = Not EPR.Value
End Sub[/td]
[/tr]
[/table]

 

[coake]

Thanks Rick, I was hoping it was that easy.
But when I use that, I get an Subscript Out of Range Error

 

[Rick Rothstein]

Thanks Rick, I was hoping it was that easy.
But when I use that, I get an Subscript Out of Range Error

While the $ signs are not needed, the argument for the Range object is valid so that cannot be causing the error. The only way I can see that error happening is if you do not have a worksheet named "HSE_Req".

 

[coake]

I carefully looked at spelling. From what little I know about error #9 it tells me the same thing - no such tab
But...
Worksheets("HSE Req").Range("$55:$55").EntireRow.Hidden = Not EPR.Value throws the subscript error;
Worksheets("HSE Req").Range("$55:$55").EntireRow.Hidden = EPR.Value actually works, just opposite. I have to uncheck the box for the row to be visible. When I check the box again, the row hides.

 

[coake]

But I do have code that works if I check the value of the checkbox.
I was hoping for less typing with a 3 liner instead of a 9 liner for 60 checkboxes.

Just curious tho... does "Not" translate to "not equal to" in this context? I'm not familiar with the Not argument.