I need help with this Subroutine!!!!

[Ayo]

What I am trying to do is as follows:
When I change the value in cell E3 or E79, I want all the rows between
("B5:B77") and ("B81:B153") that do not have the same values as in cell E3 or
E79 to be hidden. Right now I am getting an error on this line:
If Not rngAll Is Nothing Then rngAll.EntireRow.Hidden = True

Any help will be greatly appreiciated.Thanks.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim c As Range, rngAll As Range
Application.ScreenUpdating = False

If Target.Address = "$E$3" Or Target.Address = "$E$79" Then
Me.Range("E3").Value = Target.Value
Me.Range("E79").Value = Target.Value
Application.EnableEvents = False
For Each c In Me.Range("B5:B77").Cells
If c.Value <> Target.Value Then
If rngAll Is Nothing Then
Set rngAll = c
Else
Set rngAll = Union(rngAll, c)
End If
End If
Next c
If Not rngAll Is Nothing Then rngAll.EntireRow.Hidden = True

For Each c In Me.Range("B81:B153").Cells
If c.Value <> Target.Value Then
If rngAll Is Nothing Then
Set rngAll = c
Else
Set rngAll = Union(rngAll, c)
End If
End If
Next c
If Not rngAll Is Nothing Then rngAll.EntireRow.Hidden = True

Application.EnableEvents = True
End If
End Sub

 

[Scossa]

What I am trying to do is as follows:
When I change the value in cell E3 or E79, I want all the rows between
("B5:B77") and ("B81:B153") that do not have the same values as in cell E3 or
E79 to be hidden. Right now I am getting an error on this line:
If Not rngAll Is Nothing Then rngAll.EntireRow.Hidden = True

Try to change the position of .enableEvents:

If Target.Address = "$E$3" Or Target.Address = "$E$79" Then

Application.EnableEvents = False
Me.Range("E3").Value = Target.Value
Me.Range("E79").Value = Target.Value

instead of:

If Target.Address = "$E$3" Or Target.Address = "$E$79" Then

Me.Range("E3").Value = Target.Value
Me.Range("E79").Value = Target.Value
Application.EnableEvents = False


Just my two cents.

 

[Mike H]

Ayo,

The code doesn't seem right to me. In your question you say you want the
rows hidden if the vlaue in column B is different to E3 or E79 but the first
couple of lines of your code set these to the same value with thses 2 lines

Me.Range("E3").Value = Target.Value
Me.Range("E79").Value = Target.Value

So If E3 is changed to (say) 99 the first thing the code does is change it
(again) to 99 and then changes E79 to 99 and I'm pretty sure that's not what
your intending to do. So I think this is waht you want

Private Sub Worksheet_Change(ByVal Target As Range)
Dim c As Range, rngAll As Range
Application.ScreenUpdating = False

If Target.Address = "$E$3" Or Target.Address = "$E$79" Then
Application.EnableEvents = False
For Each c In Me.Range("B5:B77")
If c.Value <> Range("E3").Value And c.Value <>
Range("E79").Value Then
If rngAll Is Nothing Then
Set rngAll = c
Else
Set rngAll = Union(rngAll, c)
End If
End If
Next c
Stop

For Each c In Me.Range("B81:B153")
If c.Value <> Range("E3").Value And c.Value <>
Range("E79").Value Then
If rngAll Is Nothing Then
Set rngAll = c
Else
Set rngAll = Union(rngAll, c)
End If
End If
Next c
If Not rngAll Is Nothing Then rngAll.EntireRow.Hidden = True
Application.EnableEvents = True
End If
End Sub
--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.

 

[Mike H]

and you don't need the stop command i left in

Private Sub Worksheet_Change(ByVal Target As Range)
Dim c As Range, rngAll As Range
Application.ScreenUpdating = False

If Target.Address = "$E$3" Or Target.Address = "$E$79" Then
Application.EnableEvents = False
For Each c In Me.Range("B5:B77")
If c.Value <> Range("E3").Value And c.Value <>
Range("E79").Value Then
If rngAll Is Nothing Then
Set rngAll = c
Else
Set rngAll = Union(rngAll, c)
End If
End If
Next c

For Each c In Me.Range("B81:B153")
If c.Value <> Range("E3").Value And c.Value <>
Range("E79").Value Then
If rngAll Is Nothing Then
Set rngAll = c
Else
Set rngAll = Union(rngAll, c)
End If
End If
Next c
If Not rngAll Is Nothing Then rngAll.EntireRow.Hidden = True
Application.EnableEvents = True
End If
End Sub
--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.

 

[Wouter HM]

Hi Ayo,

I would say Keep It Simple:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim c As Range, rngAll As Range
Application.ScreenUpdating = False


If Target.Address = "$E$3" Or Target.Address = "$E$79" Then
Application.EnableEvents = False
For Each c In Me.Range("B5:B77")
If c.Value <> Range("E3").Value And c.Value <>
Range("E79").Value Then
c.EntireRow.Hidden = True
End If
Next c


For Each c In Me.Range("B81:B153")
If c.Value <> Range("E3").Value And c.Value <>
Range("E79").Value Then
c.EntireRow.Hidden = True
End If
Next c
Application.EnableEvents = True
End If
Application.ScreenUpdating = True
End Sub

HTH,

Wouter