Re: Hide Or Filter Rows Based On Cell Value
If Intersect(Target(1, 1), Range("[B]C4[/B]:I4")) ThenShould be
If Intersect(Target(1, 1), Range("[B]B4[/B]:I4")) ThenWhich is now is in my second code.
You also need to change the criteria range Names to MyCriteria1, MyCriteria2 etc
Re: Hide Or Filter Rows Based On Cell Value
My bad on removing all filters, have edited and fixed the code.
However, "<" is the less than sign, not greater than (>)
Re: Hide Or Filter Rows Based On Cell Value
Dave,
Cross posted here
http://www.mrexcel.com/forum/showthread.php?t=360383
VBA Noob
Re: Hide Or Filter Rows Based On Cell Value
Yeah I thought "<" was the less than sign, but it hides the rows greater than the number when I switched the sign from > to <.
When I enter in 23, the cell now says <=23, and it hides rows 24-35 like I need it to. When the sign was the other way >=23, it hid rows 1-22. I don't know why. Mathematically it does not make sense, but it is working, so I will let that detail go.
I noticed the wrong rows being hidden, so I switched the sign, and when it worked, I thought maybe I was wrong on which one was the greater than sign. But glad to know my math skills are NOT that horrible. 
Thanks again!
Re: Hide Or Filter Rows Based On Cell Value
QuoteI need both tables to have the rows hidden, but it will only hide the last one entered in. So when I enter in period 8, it unhides period 1-7, and only hides period 8.
I may be mistaken ( happens often :wink: ), but I think Advanced Filter will only work with one range at any time. Thus it will "turn itself off" for the current range when you next apply it to another range. If I am in error Dave, or another OzMVP, will clarify this.
Although the below code is not as elegant as the much to be preferred Select Case Method, it will get the job done:
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim rngPeriod1 As Range, rngPeriod2 As Range
Dim c1 As Range, c2 As Range
With Sheet1
Set rngPeriod1 = Sheet1.Range("C10:C44")
Set rngPeriod2 = Sheet1.Range("C52:C86")
Application.ScreenUpdating = False
If Range("$B$4").Value = "0" Then
Rows("10:44").Hidden = False
Else
For Each c1 In rngPeriod1
If c1.Value <= Range("$B$4") Then
c1.EntireRow.Hidden = False
Else
c1.EntireRow.Hidden = True
End If
Next c1
End If
If Range("$C$4").Value = "0" Then
Rows("52:86").Hidden = False
Else
For Each c2 In rngPeriod2
If c2.Value <= Range("$C$4") Then
c2.EntireRow.Hidden = False
Else
c2.EntireRow.Hidden = True
End If
Next c2
End If
End With
Application.ScreenUpdating = True
End SubRe: Hide Or Filter Rows Based On Cell Value
AAE, is correct (as usual 
) that only 1 Filter at a time per Worksheet. This why I stated;
QuoteI have edited the code so a zero in B4 will unhide ALL rows hidden by ANY filters.
Re: Hide Or Filter Rows Based On Cell Value
Your adaptation of the code will not work for at least two reasons:
You need at least the following to be met if you are going to adapt the code I gave you:
Re: Hide Or Filter Rows Based On Cell Value
Your current structure is still a problem for hiding columns because your table of hyperlinks spans some of the columns that you want hidden. At least the first three assessment tables for Unit-1 would have to remain visible at all times in order to use the table of links. (BTW - none of the links are working in the sample file.)
It would be best if you moved your "navigation table" to another worksheet. Or, at least, use only column-A and as many rows as needed to create the table of links, making sure that all of the assessment tables are below the rows used for the link table.
Re: Hide Or Filter Rows Based On Cell Value
Here is the code modified to include hiding columns
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim rngPeriod1 As Range, rngPeriod2 As Range, rngUnit1 As Range
Dim c1 As Range, c2 As Range, c3 As Range
With Sheet1
Set rngPeriod1 = Sheet1.Range("C10:C44")
Set rngPeriod2 = Sheet1.Range("C52:C86")
Set rngUnit1 = Sheet1.Range("G8:H8, K8:L8, O8:P8")
Application.ScreenUpdating = False
If Range("$B$4").Value = "0" Then
Rows("10:44").Hidden = False
Else
For Each c1 In rngPeriod1
If c1.Value <= Range("$B$4") Then
c1.EntireRow.Hidden = False
Else
c1.EntireRow.Hidden = True
End If
Next c1
End If
If Range("$C$4").Value = "0" Then
Rows("52:86").Hidden = False
Else
For Each c2 In rngPeriod2
If c2.Value <= Range("$C$4") Then
c2.EntireRow.Hidden = False
Else
c2.EntireRow.Hidden = True
End If
Next c2
End If
If Range("$T$6").Value = "0" Then
Columns("G:H").Hidden = False
Columns("K:L").Hidden = False
Columns("O:P").Hidden = False
Else
For Each c3 In rngUnit1
If c3.Value <= Range("$T$6") Then
c3.EntireColumn.Hidden = False
Else
c3.EntireColumn.Hidden = True
End If
Next c3
End If
End With
Application.ScreenUpdating = True
End SubQuoteAnd do you think it would be better if I just put the navigation table in column A?
Since this is a work in progress and subject to change based on your learning/developing experience, it is entirely up to you. However, you can improve the navigation, as shown in the attachment, by using a data validation list, a forms button, a bit of VBA using the Select Case method to go to the desired range. There are other ways to do this, such as using custom views.
Re: Hide Or Filter Rows Based On Cell Value
How you setup the navigation of your workbook/worksheets is a question unrelated to the topic of your thread title. Per the forum rules you will need to ask questions on this in a new thread.
QuoteCONCERNING THE HIDING OF ROWS AND COLUMNS:
Since I am now using this set-up, I have reworked a few things, and I would like the hidden rows to be based on a cell in another worksheet.
I have created a worksheet that is the set-up page for the teacher, and it includes entering in the number of students per period.
With this change in structure, sheet2 (or whatever sheet it is) now becomes the sheet in which the event to trigger the code must occur. Thus the code will need to reside in the module for sheet2, with applicable revision:
If [B]Sheet2.Range("$C$6")[/B].Value = "0" Then
Sheet1.Rows("11:44").Hidden = False
Else
For Each c1 In rngPeriod1
If c1.Value <= [B]Sheet2.Range("$C$6")[/B] Then
c1.EntireRow.Hidden = False
Else
c1.EntireRow.Hidden = True
End If
Next c1
End IfQuoteAnd it will hide the columns for the unit 1 trigger, but it will not hide the columns for unit 2-5 trigger. When I pressed debug, it highlighted each of this line in the unit2- unit5 range: c10.EntireColumn.Hidden = False. c11, c12, and c13 were all highlighted.
Check your variable references. You have a mismatch here:
If [COLOR="Red"][B][SIZE="2"]Range("$AO$6")[/SIZE][/B][/COLOR].Value = "0" Then
Columns("AB:AD").Hidden = False
Columns("AF:AH").Hidden = False
Columns("AS:AL").Hidden = False
Else
For Each c10 In rngUnit2
If c10.Value <= [COLOR="Blue"][B][SIZE="2"]Range("$T$6")[/SIZE][/B][/COLOR] Then
c10.EntireColumn.Hidden = False
Else
c10.EntireColumn.Hidden = True
End If
Next c10
End IfDon’t have an account yet? Register yourself now and be a part of our community!