Hide/Display rows based on Dropdown Value

[TheSkaBoss]

The spreadsheet we use at my office is clunky in a number of ways. I recently joined the company, and I'm trying to clean things up for them. I dabble in Excel, but I'm no guru, which is why I need your help. We use Excel 2010.

Right now in the model, the team can look at up to 225 sites for evaluating the deals we work. The hard part is, users have to manually hide columns they don't want to see. It can take forever to scroll below the site list to see the other useful information.

My goal is to create an input cell where users can type in however many sites they have, and it will show/hide rows accordingly. So, for example, if I input 3 in that cell, rows 14-16 would display, and rows 17-238 would be Hidden. But suppose 1 more site comes into the mix, and so I change the input value to 4, then row 17 would be Unhidden, while the rest stay Hidden. It seems like it shouldn't be that complicated, but I can't figure it out.

If I recall correctly, I'll have to use two macros, with the first one Calling the second, based on the value of the cell changing.

I think I'll need to create a Dynamic Named Range from X:238 that changes based on the number chosen. For example, if 1 is chosen, then X would be 15, for row 15. If I chose 3, then X would be 17, for row 17. That way it hides rows X:238. I'm looking into how to do this, but no luck yet.

So right now I have the dropdown/input cell named NumberOfSites. When that value changes, it should trigger the macro SiteNumberSelection

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim KeyCells As Range
   Set KeyCells = Range("NumberOfSites")
   Call SiteNumberSelection
  
   End Sub

So I have that part, but it doesn't seem to do much.

Sub SiteNumberSelection()
'
' SiteNumberSelection Macro
' Shows the number of rows that corresponds with sites to be modeled
'

'
    Range("NumberOfSites").Select
    ActiveCell.FormulaR1C1 = "4"
    Rows("18:238").EntireRow.Hidden = True
End Sub

I know there is a way to do this, but I'm just not putting two and two together here, it seems like. Can anyone help me figure this out?

 

[WarPigl3t]

I have a question. You said that people have to scroll down the list to find the information they need. Therefore that implies that the data they need is not in the first rows. It is mixed up in many locations throughout the list. But you also said that you want code that you type a number into a cell and it hides all rows under that number of rows. So if you type in 3, then it will unhide the top 3 from the list and hide the rest of the list. Well then if the data you are looking for is mixed up in random locations throughout the list, how is hiding all but the top designated rows help you?

 

[TheSkaBoss]

I have a question. You said that people have to scroll down the list to find the information they need. Therefore that implies that the data they need is not in the first rows. It is mixed up in many locations throughout the list. But you also said that you want code that you type a number into a cell and it hides all rows under that number of rows. So if you type in 3, then it will unhide the top 3 from the list and hide the rest of the list. Well then if the data you are looking for is mixed up in random locations throughout the list, how is hiding all but the top designated rows help you?

The rows I'm hoping to show/hide are only part of the inputs--the things there are unique to each site. There are a few sections located further down that require some input, but are not site-specific.

Example:

So after inputting site-specific info (which is ALWAYS needed), there are other sections that often (but not always) need to be filled out. That's why showing/hiding the rows would work better for the company than simply moving these other sections to the top.