# Show/Hide Row Range Based on Cell Value

#### twothings

##### Board Regular
Hello all and thank you for your assistance.

I have searched forums thoroughly for a solution to my problem without success so seek your expertise for an answer.

Situation
I have a list box with the following 13 options:

 Show All July 14 August 14 September 14 October 14 November 14 December 14 January 15 February 15 March 15 April 15 May 15 June 15

<colgroup><col></colgroup><tbody>
</tbody>

Depending on what is selected, cell C2 gets a number between 1 and 13.

In row ranges B8:B372, I have the following date ranges for a local financial year:

 01/07/14 02/07/14 03/07/14 04/07/14 05/07/14 ... 30/06/15

<colgroup><col></colgroup><tbody>
</tbody>

Problem
What I need to occur is the following:

If C2 = 1, all row dates show
If C2 = 2, only show July 14 dates (1/7/14-31/7/14). All other rows in range B8:B372 hide.
If C2 = 3, only show August 14 dates, others in range hide
If C2 = 4, only show Sept 14 dates, others in range hide
If C2 = 5, only show Oct 14 dates, others in range hide
...
If C2 = 13, only show June 15 dates, others in range.

Thank you for taking the time to read this and provide assistance.

Scott

### Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.

#### CSyx

##### Board Regular
Scott,

Probably not the most efficient way but this might get you started. Just update the code to include all of the 13 possible cases.

Code:
``````Private Sub ShowDateRange()

Select Case True

Case Sheet1.Range("C2").Value = 1
Rows("8:372").EntireRow.Hidden = False

Case Sheet1.Range("C2").Value = 2
Rows("8:372").EntireRow.Hidden = False
Rows("39:372").EntireRow.Hidden = True

End Select
End Sub``````

#### twothings

##### Board Regular
Hello CSyx
Thank you for your suggestion. Unfortunately I am unable to get this to work. I have gone over it and even tried hiding rows then changing C2 to value 1 to unhide everything. Nothing happens however. It has been a long time since I played around with VB but I am confident all cell references etc are correct.
Scott

#### twothings

##### Board Regular
Also, if I could add that it needs to be dynamic for changes to cell C2. thank you

#### CSyx

##### Board Regular
Scott,

No worries. We will get it running. To make the code dynamic we simply need to paste the code into the worksheet change event. Please right click on the worksheet tab and select view code. Then at the top of the screen change the drop down menu from "General" to "Worksheet". Lastly paste your code into this new sub.

Chris

#### twothings

##### Board Regular
Thank you, it works now but the show/hides don't update without a user clicking elsewhere on the sheet. Is there a way around this?

My code currently...

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Select Case True

Case Sheet1.Range("B2").Value = 1 'Show All
Rows("8:372").EntireRow.Hidden = False

Case Sheet1.Range("B2").Value = 2 'July
Rows("8:372").EntireRow.Hidden = True
Rows("8:38").EntireRow.Hidden = False

Case Sheet1.Range("B2").Value = 3 'August
Rows("8:372").EntireRow.Hidden = True
Rows("39:69").EntireRow.Hidden = False

Case Sheet1.Range("B2").Value = 4 'September
Rows("8:372").EntireRow.Hidden = True
Rows("70:99").EntireRow.Hidden = False

Case Sheet1.Range("B2").Value = 5 'October
Rows("8:372").EntireRow.Hidden = True
Rows("100:130").EntireRow.Hidden = False

Case Sheet1.Range("B2").Value = 6 'November
Rows("8:372").EntireRow.Hidden = True
Rows("131:160").EntireRow.Hidden = False

Case Sheet1.Range("B2").Value = 7 'December
Rows("8:372").EntireRow.Hidden = True
Rows("161:191").EntireRow.Hidden = False

Case Sheet1.Range("B2").Value = 8 'January
Rows("8:372").EntireRow.Hidden = True
Rows("192:222").EntireRow.Hidden = False

Case Sheet1.Range("B2").Value = 9 'February
Rows("8:372").EntireRow.Hidden = True
Rows("223:250").EntireRow.Hidden = False

Case Sheet1.Range("B2").Value = 10 'March
Rows("8:372").EntireRow.Hidden = True
Rows("251:281").EntireRow.Hidden = False

Case Sheet1.Range("B2").Value = 11 'April
Rows("8:372").EntireRow.Hidden = True
Rows("282:311").EntireRow.Hidden = False

Case Sheet1.Range("B2").Value = 12 'May
Rows("8:372").EntireRow.Hidden = True
Rows("312:342").EntireRow.Hidden = False

Case Sheet1.Range("B2").Value = 13 'June
Rows("8:372").EntireRow.Hidden = True
Rows("343:372").EntireRow.Hidden = False

End Select
End Sub

#### Teeroy

##### Well-known Member
I think I would have gone with an Autofilter approach (but I don't know what your data is to be sure). For the select case you only put in the condition you are evaluating once, try:

Code:
``````Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address <> "\$B\$2" Then Exit Sub
Rows("8:372").EntireRow.Hidden = True

Select Case Me.Range("B2").Value

Case Is = 1 'Show All
Rows("8:372").EntireRow.Hidden = False

Case Is = 2 'July
Rows("8:38").EntireRow.Hidden = False

Case Is = 3 'August
Rows("39:69").EntireRow.Hidden = False

Case Is = 4 'September
Rows("70:99").EntireRow.Hidden = False

Case Is = 5 'October
Rows("100:130").EntireRow.Hidden = False

Case Is = 6 'November
Rows("131:160").EntireRow.Hidden = False

Case Is = 7 'December
Rows("161:191").EntireRow.Hidden = False

Case Is = 8 'January
Rows("192:222").EntireRow.Hidden = False

Case Is = 9 'February
Rows("223:250").EntireRow.Hidden = False

Case Is = 10 'March
Rows("251:281").EntireRow.Hidden = False

Case Is = 11 'April
Rows("282:311").EntireRow.Hidden = False

Case Is = 12 'May
Rows("312:342").EntireRow.Hidden = False

Case Is = 13 'June
Rows("343:372").EntireRow.Hidden = False

Case Else
Rows("8:372").EntireRow.Hidden = False 'on incorrect data show all

End Select
End Sub``````

#### twothings

##### Board Regular
Hello Teeroy

Thanks for your time. I paasted in your suggestion and removed other coding and it works if I change the number manually and press enter or tab out of the cell. However when the number changes by changing the selection in the List Box, nothing changes, even when clicking elsewhere within the worksheet. Any idea how to get this to work? Thank you again for your time, it is appreciated!!!

#### Teeroy

##### Well-known Member
When you were referring to a different cell and the code had changed I thought you must have discarded the ListBox.

Try the following, assuming your ListBox is "ListBox1":

Code:
``````Private Sub ListBox1_Change()

Rows("8:372").Hidden = True

Select Case Me.Range("B2").Value

Case Is = 1 'Show All
Rows("8:372").EntireRow.Hidden = False

Case Is = 2 'July
Rows("8:38").EntireRow.Hidden = False

Case Is = 3 'August
Rows("39:69").EntireRow.Hidden = False

Case Is = 4 'September
Rows("70:99").EntireRow.Hidden = False

Case Is = 5 'October
Rows("100:130").EntireRow.Hidden = False

Case Is = 6 'November
Rows("131:160").EntireRow.Hidden = False

Case Is = 7 'December
Rows("161:191").EntireRow.Hidden = False

Case Is = 8 'January
Rows("192:222").EntireRow.Hidden = False

Case Is = 9 'February
Rows("223:250").EntireRow.Hidden = False

Case Is = 10 'March
Rows("251:281").EntireRow.Hidden = False

Case Is = 11 'April
Rows("282:311").EntireRow.Hidden = False

Case Is = 12 'May
Rows("312:342").EntireRow.Hidden = False

Case Is = 13 'June
Rows("343:372").EntireRow.Hidden = False

Case Else
Rows("8:372").EntireRow.Hidden = False 'on incorrect data show all

End Select

End Sub``````

Replies
0
Views
281
Replies
3
Views
80
Replies
3
Views
218
Replies
0
Views
83
Replies
26
Views
665

1,191,165
Messages
5,985,040
Members
439,935
Latest member
Monty238

### 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.

### Which adblocker are you using?

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

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