Hide a Variable Number of Rows

[Matman67]

Hello,

Thanks in advance for any help. I tried another solution found here, but could not adapt it to my needs because my VB knowledge is too limited.

A cell named "No_App" contains an integer 1-26.
Based on the value of this number (N) I want to hide all except the first N rows in the range of rows 11-36.

So if "No_App" is 1, rows 12–36 will become hidden when I run the Macro.
If "No_App" is 10, rows 21–36 will become hidden when I run the Macro.

When I run the macro again, rows which were previously hidden may need to become un-hidden based on the new value.

I also need to integrate this into the following existing Macro.

Sub hpcopy()
'
' hpcopy Macro
'
'
Sheets("HPTOOL").Select
Range("G11:X36").Select
Selection.Clear
Sheets("TABLES").Select
Range("B26:B51").Select
Selection.Copy
Sheets("HPTOOL").Select
Range("firsthp").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

End Sub

 

[footoo]

On what sheet is "No_App" ?
On what sheet do the rows need to be hidden ?

 

[Matman67]

Thank you.
No_App is on HPTOOL.
That's also where the rows to be hidden are.

I could probably consolidate it all to one sheet.

 

[Fluff]

How about

With Sheets("HPTOOL")
   .Rows("11:36").Hidden = False
   .Rows(11 + Range("No_App").Value & ":36").Hidden = True
End With

 

[Peter_SSs]

How about

With Sheets("HPTOOL")
   .Rows("11:36").Hidden = False
   .Rows(11 + Range("No_App").Value & ":36").Hidden = True
End With

If No_App = 26 that code
a) Only shows 25 rows of the required range, not 26 and
b) Permanently hides row 37

Suggest this modification

Sub Hide_Rows()
  With Sheets("HPTOOL")
    .Rows("11:36").Hidden = False
    If Range("No_App").Value < 26 Then .Rows(11 + Range("No_App").Value & ":36").Hidden = True
  End With
End Sub