Evaluating Strings in Range

[Bass71]

Hi;

In a range of 1 X 6,I'm trying to hide rows those rows whose cells meet the criteria of either containing the string "PH" or a combination of nulls and "PH". Any other strings found in any given row, with or without nulls, would necessitate keeping row visible. Here's what I've got thus far...

Range("A1").Select

Dim c As Range
For Each c In Range(ActiveCell.Offset(0, 0), ActiveCell.Offset(0, 6))
If c.Value = "PH" Or c.Value <> "" Then
Selection.EntireRow.Hidden = True
ActiveCell.Offset(1, 0).Select
Else
ActiveCell.Offset(1, 0).Select
End If
Next


Thanks..........RO

 

[Loomah]

What exactly doesn't do what you want?

My first impressions were that the code could be cut down but apart from that the only thing tha jumps out is that you are testing the range A1:F1. Was that the intention?

If you are trying to hide fows that meet a criteria you couald start with this amendment to your code. If this is moving in the wrong direction plaese come back and let me know.

Trying to understand your criteria this is how I read it
1) cell contains PH - hide it
2) Cell contains anything (in other words isn't EMPTY) - hide it
3) Cell is EMPTY - leave row visible

For Each c In Range(Range("A1"), Range("A1").Offset(0, 6))
    If c = "PH" Or c <> "" Then
        c.EntireRow.Hidden = True
    End If
Next

If you wonder why/how I've cut your code back have a look in the FAQ section for a FAQ by SkipVought on making code run faster (can't remember the exact title!)

;-)

If a man says something and there are no women there to hear him, is he still wrong?
How do I get the best answers?​

 

[Bass71]

This is the criteria:
PH will appear in every range at least once.
1. If cells in range contain any combination of PH and "", hide them
2. If cells in range contain anything other than PH and blank, leave visible

 

[PHV]

I guess you wanted this test:
If c = "PH" Or Trim(c.Text & "") = "" Then

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886