Hide/Display rows based on Dropdown Value

TheSkaBoss

New Member
Joined
Aug 12, 2014
Messages
5
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.

eKgqErj.png


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

Code:
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.

Code:
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?
 

Some videos you may like

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.

WarPigl3t

Well-known Member
Joined
May 25, 2014
Messages
1,609
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

New Member
Joined
Aug 12, 2014
Messages
5
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:

0KQrXhs.png


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.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,935
Messages
5,627,708
Members
416,268
Latest member
zRGZgDNnmaPhepwviEou

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top