Thank you very much. It is working like a dream
--
Regards,

Martin


"Bob Phillips" wrote:

Simple way.

For i = 1 To Cells(Rows.Count,"A").End(xlUp).Row
If Cells(i,"A").Value = "Pears" then
If rng Is Nothing Then
Set rng = Cells(i,"A")
Else
Set rng = Union(rng,Cells(i,"A")
End If
End If
Next i

If Not rng Is Nothing Then
rng.Name = "Pears"
End If

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

"Martin" wrote in message
...
Dear all,

I have a spreadsheet that looks like this:

Column A
Apple
Apple
Apple
Pear
Pear
Pear
Pear
Orange
Orange

The number of rows with Pears change from time to time and I want a macro
to
find all the cell with Pear and then name the range as Pears.

Does anyone know how to do this? Any help much appreciated.

--
Regards,

Martin