SpecialCells and a Special Character

[Stuart]

I use
Set rng = .Columns(5).SpecialCells(xlConstants, xlTextValues)
to test for text values.

I have a special character defined by the variable CatoChar
which is dimmed as a String.

Can the above code be adapted to find CatoChar, please?

Regards.

 

[Dick Kusleika]

Stuart

You can loop through the cells and find only those cells with CatoChar.
Here's an example

Sub CatoOnly()

Dim Rng As Range
Dim CatoRng As Range
Dim FndRng As Range
Dim FirstAdd As String
Const CatoChar As String = "great"

Set Rng = Sheet1.Columns(5).SpecialCells(xlCellTypeConstants, xlTextValues)

Set FndRng = Rng.Find(CatoChar)

If Not FndRng Is Nothing Then
FirstAdd = FndRng.Address
Do
If CatoRng Is Nothing Then
Set CatoRng = FndRng
Else
Set CatoRng = Union(CatoRng, FndRng)
End If

Set FndRng = Rng.Find(CatoChar, FndRng)
Loop Until FndRng.Address = FirstAdd
End If

MsgBox CatoRng.Address

End Sub

 

[Stuart]

Many thanks.

That seems quite fast. Will have to find some way to use
it in my routine.

Regards.