Help with hiding rows based on cell criteria

[=HPSF=RMP]

I've searched far and wide. I'm close, but can't get it to work.


Here is my code:

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("n1")) Is Nothing Then
  Exit Sub
 Else
 Application.ScreenUpdating = False
 Application.Calculation = xlCalculationManual
Rows("8:28").Hidden = True
    Select Case Range("n1").Value
     Case "1"
     Rows("8:12,16:28").Hidden = False
     Case "2"
     Rows("8:10,12:19,22:28").Hidden = False
     Case "3"
     Rows("9:10,12,16,18:28").Hidden = False
     Case "4"
     Rows("9:10,12:16,18:19,22:28").Hidden = False
End Select
End If
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
End Sub

I have a formula in N1 that will give me a number from 1-5.
I want to reveal rows based on the result of the formula in N1.
the forumla in N1 is:

=IF(L1="Inbound ",1,IF(L1="Inbound 3rd Party",2,IF(L1="Outbound ",3,IF(L1="Outbound 3rd Party",4,5))))

The formula for L1 is a concantination of two other cells:

=F3&" "&J1

Hope this makes sense.
Any help on this would be great thanks

 

[Smitty]

If N1 is changing as the result of a formula you'll need to use the Calculate event instead. The Change event won't fire when a cell changes as a result of a calculation.

Just wrap your Select case statement in Worksheet_Calculate, although I'd get rid of the Application calls as they're unnecessary for this.

HTH,

 

[=HPSF=RMP]

I've done as you suggested and i'm getting the following error:
Compile error: Procedure declaration does not match description of event or procedure having the same name.

Did I do it correctly?
thanks

Private Sub Worksheet_Calculate(ByVal Target As Range)
If Intersect(Target, Range("n1")) Is Nothing Then
  Exit Sub
 Else
Rows("8:28").Hidden = True
    Select Case Range("n1").Value
     Case "1"
     Rows("8:12,16:28").Hidden = False
     Case "2"
     Rows("8:10,12:19,22:28").Hidden = False
     Case "3"
     Rows("9:10,12,16,18:28").Hidden = False
     Case "4"
     Rows("9:10,12:16,18:19,22:28").Hidden = False
End Select
End If
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
End Sub

 

[lenze]

The Calculate procedure does not set a Target.

Private Sub Worksheet_Calculate(ByVal Target as Range)
If Intersect(Target, Range("n1")) Is Nothing Then
  Exit Sub
 Else 'Delete the lines in red

However, If N1 is changed via formula, you can probably use the Change Event with a precedent. What formula is in N1?

lenze

 

[Smitty]

See if this does what you want:

<font face=Calibri><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> Worksheet_Calculate()<br>    Rows("8:28").Hidden = <SPAN style="color:#00007F">True</SPAN><br>        <SPAN style="color:#00007F">Select</SPAN> <SPAN style="color:#00007F">Case</SPAN> Range("N1").Value<br>            <SPAN style="color:#00007F">Case</SPAN> "1"<br>               Range("8:12, 16:28").EntireRow.Hidden = <SPAN style="color:#00007F">False</SPAN><br>            <SPAN style="color:#00007F">Case</SPAN> "2"<br>               Range("8:10,12:19,22:28").EntireRow.Hidden = <SPAN style="color:#00007F">False</SPAN><br>            <SPAN style="color:#00007F">Case</SPAN> "3"<br>               Range("9:10,12:12,16:16,18:28").EntireRow.Hidden = <SPAN style="color:#00007F">False</SPAN><br>            <SPAN style="color:#00007F">Case</SPAN> "4"<br>               Range("9:10,12:16,18:19,22:28").EntireRow.Hidden = <SPAN style="color:#00007F">False</SPAN><br>        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Select</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

(Heya Lenze, back in town?)

 

[=HPSF=RMP]

Still doesnt seem to work.

The Calculate procedure does not set a Target.

Private Sub Worksheet_Calculate(ByVal Target as Range)
If Intersect(Target, Range("n1")) Is Nothing Then
  Exit Sub
 Else 'Delete the lines in red

However, If N1 is changed via formula, you can probably use the Change Event with a precedent. What formula is in N1?

lenze

the forumla in N1 is:

=IF(L1="Inbound ",1,IF(L1="Inbound 3rd Party",2,IF(L1="Outbound ",3,IF(L1="Outbound 3rd Party",4,5))))

The formula for L1 is a concantination of two other cells:

=F3&" "&J1

 

[lenze]

How are F3 and J1 changed (Did you mean J3?)?

lenze

 

[=HPSF=RMP]

Sorry, changed it around..
Its supposed to be a concatenation of f3 and G1s.

 

[=HPSF=RMP]

I've changed it around.
Still getting an error and cannot figure out what the issue is.

Here is the new code:

Private Sub Worksheet_Calculate()
    Select Case Range("N1").Value
        Case "1"
            Rows("8:12").Hidden = False
            Rows("13:15").Hidden = True
            Rows("16:28").Hidden = False
        Case "2"
            Rows("8:10").Hidden = False
            Rows("11").Hidden = True
            Rows("12:19").Hidden = False
            Rows("20:21").Hidden = True
            Rows("22:28").Hidden = False
        Case "3"
            Rows("8").Hidden = True
            Rows("9:10").Hidden = False
            Rows("11").Hidden = True
            Rows("12").Hidden = False
            Rows("13:15").Hidden = True
            Rows("16").Hidden = False
            Rows("17").Hidden = True
            Rows("18:28").Hidden = False
        Case "4"
            Rows("8").Hidden = True
            Rows("9:10").Hidden = False
            Rows("11").Hidden = True
            Rows("12").Hidden = False
            Rows("13:15").Hidden = True
            Rows("16").Hidden = False
            Rows("17").Hidden = True
            Rows("18:19").Hidden = False
            Rows("20:21").Hidden = True
            Rows("22:28").Hidden = False
    End Select
End Sub

Thanks

 

[lenze]

Well, the 1st thing I notice is your formula in N1 return a numeric value and your code is looking for a text value. Remove the ""s in your code

Case 1

lenze