IF - THEN question

[Guest]

In my spreadsheet, col. A contains quantities, col. B contains prices. In
column D, I have formulas multiplying quantity times price. Column D cells
are also formatted as currency, and they display $0.00 when no quantity is
present.

I've named col. D as a range ("HideRows"). My goal is to have my code
search my named range, and for each cell containing $0.00, if the
corresponding quantity cell is empty, hide the row. If the quantity is
entered as zero, I don't want the row to be hidden. My code is below, but it
is not recognizing my conditions. What am I missing? Thanks for your help!

Sub HidePLRows()
Dim cell As Range
Application.ScreenUpdating = False
Range("HideRows").Select
For Each cell In Range("HideRows")
If ActiveCell.Value = "$0.00" And ActiveCell.Offset(0, -3).Value = ""
Then
Selection.EntireRow.Hidden = True
Else
ActiveCell.Offset(1, 0).Select
End If
Next
Range("A14").Select
Application.ScreenUpdating = True
End Sub

 

[Bob Phillips]

The cell may be formatted as $0.00, but the value is 0.

And then, if you iterate through a range using an object called cell, you
should use cell, not activecell.

And then, because you are iterating through in a 'look-ahead' type of loop,
there is not need to slect anything.

Sub HidePLRows()
Dim cell As Range
Application.ScreenUpdating = False
For Each cell In Range("HideRows")
If cell.Value = 0 And cell.Offset(0, -3).Value = "" Then
cell.EntireRow.Hidden = True
End If
Next
Range("A14").Select
Application.ScreenUpdating = True
End Sub


--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

 

[Mark Lincoln]

Just a quick guess, not tested. Perhaps you need to change:

If ActiveCell.Value = "$0.00"

to

If ActiveCell.Value = 0

 

[Scoops]

Hi Steve C

I think "" and 0 are being treated as equals.

Try: If ActiveCell.Value = "$0.00" And IsEmpty(ActiveCell.Offset(0,
-3).Value)

Moreover, you can dispense with your ActiveCell refrences and
Selections because you've told Excel to look at Each cell

Try: If cell = "$0.00" And IsEmpty(cell.Offset(0, -3)) Then
cell.EntireRow.Hidden = True

There's no need to Select the next cell

Regards

Steve

 

[Guest]

Bob,

That's what I was looking for. Thanks a ton!