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:

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

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.
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

M
  • Question
Need multiple buttons to hide/show rows on another tab
Replies
8
Views
375
skillilea
S
M
  • Solved
Show/Hide a textbox based on the value of a cell
Replies
9
Views
284
Peter_SSs
Peter_SSs
J
  • Question
VBA Macro for hiding and unhiding rows is rearranging cells
2
Replies
14
Views
919
dmt32
D
B
  • Solved
Another VBA question to hide/unhide rows based on cell value in another sheet
2
Replies
12
Views
379
kevin9999
K
WildBurrow
  • Question
Copy/Paste data to next empty row within a range - WITHOUT shifting subsequent rows up
Replies
0
Views
101
WildBurrow
WildBurrow

Forum statistics

Threads
1,214,541
Messages
6,120,110
Members
448,945
Latest member
Vmanchoppy

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