Hid up to 100 individual rows from cell in another sheet

[SReilly]

Hello there,

Looking for a bit of help at hiding rows in a sheet called "Summary" based on cell E35 in a sheet called "Basics".
I can get this to work using the code below the problem is that I will want the value to go to 100 and up to 100 rows (from row 24 on) to be hidden. WI'd also like all rows to be unhidden if E35 is blank. Would anyone have any suggestions on how this is possible using a loop function?

Private Sub Worksheet_Calculate()
 Dim Summaries,As Range

 Set Summaries = Sheets("Basics").Range("E35")
  
 If Summaries.Value = "1" Then
 Sheets("Summary").Rows("23:23").EntireRow.Hidden = False
 Sheets("Summary").Rows("24:32").EntireRow.Hidden = True
 
 ElseIf Summaries.Value = "2" Then
 Sheets("Summary").Rows("23:24").EntireRow.Hidden = False
 Sheets("Summary").Rows("25:32").EntireRow.Hidden = True
 
 ElseIf Summaries.Value = "3" Then
 Sheets("Summary").Rows("23:25").EntireRow.Hidden = False
 Sheets("Summary").Rows("26:32").EntireRow.Hidden = True

 Else
 Sheets("Summary").Rows("23:32").EntireRow.Hidden = False

 End If
 End Sub

Thanks so much for your help!

 

[JLGWhiz]

Give this a try

Private Sub Worksheet_Calculate()
 Dim cnt As Long
 cnt = Sheets("Basics").Range("E35").Value
 If cnt > 0 Then
    Sheets("Summary").Rows("23:" & 22 + cnt).Hidden = False
    Sheets("Summary").Rows(23 + cnt & ":100").Hidden = True
 Else
    Sheets("Summary").Rows("23:100").Hidden = False
 End If
 End Sub

 

[SReilly]

Thanks so much for your quick reply JLGWhiz - it looks so simple when you see it like that!
I wish I could think of these things More practise needed I guess!
Thanks again JLGWhiz, really appreciate it!

 

[JLGWhiz]

Thanks so much for your quick reply JLGWhiz - it looks so simple when you see it like that!
I wish I could think of these things More practise needed I guess!
Thanks again JLGWhiz, really appreciate it!

You're welcome,
regards, JLG