Hide / Unhide a Number of Rows Based on Value in Cell

[jason55]

Hi guys, hope you can help me out here as I'm new to VBA. I'm trying to forecast tenant rent rolls for a property and have set for a max of 50 tenants. But based on a numerical value in a cell, which denotes how many tenants there are, I would like to hide the unused rows in the worksheet. E.g. if there are only 10 tenants, I would like the remaining 40 rows to be hidden. Below is an extract of what I have done, and the list goes down to 50 cases which is quite tedious. Wondering if there's a better way to do this? Thanks guys! Really appreciate your help on this.

Rows("10:61").EntireRow.Hidden = True


Select Case Range("A1").Value
Case Is = 1
Rows("10:11").EntireRow.Hidden = False
Case Is = 2
Rows("10:12").EntireRow.Hidden = False
Case Is = 3
Rows("10:13").EntireRow.Hidden = False

​

 

[JoeMo]

What's the first row of the 50 rows?

 

[jason55]

That would be Row 11 JoeMo.

 

[footoo]

Rows("10:61").Hidden = True
Rows("10:" & 10 + [A1]).Hidden = False

 

[jason55]

Wow that's short and sweet and it works! Thanks footoo!

 

[JoeMo]

That would be Row 11 JoeMo.

Untested.

Sub HideIf()
Application.ScreenUpdating = False
Rows("11:50").Hidden = True
Rows("11:" & 11 + Range("A1").Value - 1).Hidden = False
Application.ScreenUpdating = True
End Sub