Show/Hide Row Range Based on Cell Value

T

twothings

Board Regular
Joined
Jul 9, 2011
Messages
50
Office Version
  1. 365
Platform
  1. Windows
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:

[TABLE="width: 94"]
<colgroup><col></colgroup><tbody>[TR]
[TD]Show All[/TD]
[/TR]
[TR]
[TD]July 14[/TD]
[/TR]
[TR]
[TD]August 14[/TD]
[/TR]
[TR]
[TD]September 14[/TD]
[/TR]
[TR]
[TD]October 14[/TD]
[/TR]
[TR]
[TD]November 14[/TD]
[/TR]
[TR]
[TD]December 14[/TD]
[/TR]
[TR]
[TD]January 15[/TD]
[/TR]
[TR]
[TD]February 15[/TD]
[/TR]
[TR]
[TD]March 15[/TD]
[/TR]
[TR]
[TD]April 15[/TD]
[/TR]
[TR]
[TD]May 15[/TD]
[/TR]
[TR]
[TD]June 15[/TD]
[/TR]
</tbody>[/TABLE]

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:

[TABLE="width: 98"]
<colgroup><col></colgroup><tbody>[TR]
[TD]01/07/14[/TD]
[/TR]
[TR]
[TD]02/07/14
03/07/14
04/07/14
05/07/14
...
30/06/15[/TD]
[/TR]
</tbody>[/TABLE]


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
 
Sort by date Sort by votes
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
 
Upvote 0
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
 
Upvote 0
Also, if I could add that it needs to be dynamic for changes to cell C2. thank you
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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!!!

keclki.jpg
 
Upvote 0
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
 
Upvote 0
You must log in or register to reply here.

Similar threads

Z
  • Question Question
VBA: Date Format Issues with Data Validation
Replies
3
Views
405
zero269
Z
M
  • Question Question
Need multiple buttons to hide/show rows on another tab
Replies
8
Views
899
skillilea
S
D
  • Question Question
Locate first instance of all dates in a column and hide all remaining rows
Replies
4
Views
212
Peter_SSs
Peter_SSs
O
  • Solved
show data on userform vertically based on combobox for multiple ranges
Replies
2
Views
162
Omran Y
O
R
  • Solved
Excel/Google Sheets COUNTIFS formula to count rows, but with a substring in the Range.
2
Replies
11
Views
722
Fluff
Fluff

Share this page

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
Back
Top