VBA and "If"?!

[Jo]

I have this code runs fine but, for Zone4=7, its row gets hidden
although it shouldn't according to the code below. What could I be
missing?

If Zone5 = 0 Then
Range("Zone5").Select
Selection.EntireRow.Hidden = True
End If

If Zone4 = 0 Then
Range("Zone4").Select
Selection.EntireRow.Hidden = True
End If

If Zone3 = 0 Then
Range("Zone3").Select
Selection.EntireRow.Hidden = True
End If

If Zone2 = 0 Then
Range("Zone2").Select
Selection.EntireRow.Hidden = True
End If

 

[Guest]

I can see that you have a named range called Zone4 but it also appears that
you have a variable called Zone4. Where is your variable Zone4 declared and
how is the value initialized? I guess what I am saying is post the rest of
your code...

 

[George Nicholson]

Are all 4 Named Ranges on different rows? No overlaps? (Have you
doublechecked?)

My guess would be that either Zone5, 3 or 2 is on the same row as (or has a
range that overlaps) Zone4 and that one of them equals zero, causing Zone 4
to be hidden as well.

HTH,

 

[Jo]

I can see that you have a named range called Zone4 but it also appears that
you have a variable called Zone4. Where is your variable Zone4 declared and
how is the value initialized? I guess what I am saying is post the rest of
your code...
--
HTH...

Jim Thomlinson









- Show quoted text -

Zone5, ...Zone2 are cell names only and I have them nowhere defined in
the code. Simply I am telling the code this: where Zone5=0, hide the
whole row, same to Zone4,..Zone2!

How I can fix this? Thanks

 

[Jo]

Are all 4 Named Ranges on different rows? No overlaps? (Have you
doublechecked?)

My guess would be that either Zone5, 3 or 2 is on the same row as (or has a
range that overlaps) Zone4 and that one of them equals zero, causing Zone 4
to be hidden as well.

HTH,










- Show quoted text -

George,

No overlapping for sure!

 

[Jo]

I can see that you have a named range called Zone4 but it also appears that
you have a variable called Zone4. Where is your variable Zone4 declared and
how is the value initialized? I guess what I am saying is post the rest of
your code...
--
HTH...

Jim Thomlinson









- Show quoted text -

Whole code is below:

Sub ZeroPointChangeA()
'
' ZeroPointChange Macro
' Macro recorded 8/2/2007 by Michael Sultan
'

'
ActiveSheet.ChartObjects("Chart 46").Activate
ActiveChart.Axes(xlValue).Select
With ActiveChart.Axes(xlValue)
.MinimumScale = Names("BaseCPM1").RefersToRange.Value
.MaximumScaleIsAuto = True
.MinorUnitIsAuto = True
.MajorUnitIsAuto = True
.Crosses = xlAutomatic
.ReversePlotOrder = False
.ScaleType = xlLinear
.DisplayUnit = xlNone
End With
ActiveChart.PlotArea.Select
ActiveChart.Axes(xlCategory).Select
With ActiveChart.Axes(xlCategory)
.MinimumScale = Names("BaseCR1").RefersToRange.Value * 100
.MaximumScaleIsAuto = True
.MinorUnitIsAuto = True
.MajorUnitIsAuto = True
.Crosses = xlAutomatic
.ReversePlotOrder = False
.ScaleType = xlLinear
.DisplayUnit = xlNone
End With

If Zone5 <= 0 Then
Range("Zone5").Select
Selection.EntireRow.Hidden = True
End If

If Zone4 <= 0 Then
Range("Zone4").Select
Selection.EntireRow.Hidden = True
End If

If Zone3 <= 0 Then
Range("Zone3").Select
Selection.EntireRow.Hidden = True
End If

If Zone2 <= 0 Then
Range("Zone2").Select
Selection.EntireRow.Hidden = True
End If

End Sub

 

[Guest]

So Zone5, Zone4 and ... are only named ranges. In that case you can not just
reference them as if they were variables. Give this a try...

If Range("Zone5").Value = 0 Then
Range("Zone5").EntireRow.Hidden = True
End If

If Range("Zone4").Value = 0 Then
Range("Zone4").EntireRow.Hidden = True
End If

If Range("Zone3").Value = 0 Then
Range("Zone3").EntireRow.Hidden = True
End If

If Range("Zone2").Value = 0 Then
Range("Zone2").EntireRow.Hidden = True
End If

 

[Guest]

Just as an observation, the OP could probably shorten these if blocks to:

For i = 2 to 5
With Range("Zone" & i)
.EntireRow.Hidden = (.Value = 0)
End With
Next i

but the OP has used both <= 0 and = 0 as the condition for hiding the row(s)
and I'm assuming the named ranges are single cells. Otherwise would probably
have to loop through the range and hide/unhide every row.