Howso I sort a range based on partial text in a cell

[PoohBear2000]

I have a range in which the first column holds FPDN 5xx or PDN xxx numbers. How can I filter so that I show only FPDN items, regardless of what number follows them?

This is my code at this point.

Dim R As Range, Cel As Range
Set R = Range("PDNNum")
For Each Cel In R
If Cel.Value <> "FPDN" Then _
Cel.EntireRow.Hidden = True

Obviously the "<> "FPDN" " reference does not work because the number following the descriptor is messing things up. Is there a "contains" reference I could use?

Here is a sample of the range and I need to filter out for FPDN:

FPDN 501 15-Sep-06 $800,000 Joe Black
FPDN 502 18-Sep-06 $36,000 Joe Black
FPDN 503 20-Oct-06 $40,000 Joe Black
PDN 001 15-Jan-06 $45,000 Joe Black
PDN 002 19-Jun-06 $70,000 Joe Black
PDN 003 6-Jul-06 N/A Joe Black
PDN 004 1-Aug-06 $110,000 Joe Black

 

[Bong]

Use the Like operator:

result = string Like pattern

In your case, I think it would be:

If !(cel.value Like "FPDN*") Then Cel.EntireRow.Hidden = True

_________________
Bob Rashkin

 

[PoohBear2000]

Thanks! That worked perfectly!

 

[xlbo]

Why loop? use autofilter and the "Does Not Begin With" operator

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question