Try this

Sub test()
Call AddName("Pear")
End Sub

Public Sub AddName(ByVal Fruit As String)
Dim wks As Worksheet
Dim rngToSearch As Range
Dim rngFound As Range
Dim rngFoundAll As Range
Dim strFirstAddress As String

Set wks = ActiveSheet
Set rngToSearch = wks.Columns("A")
Set rngFound = rngToSearch.Find(What:=Fruit, _
LookIn:=xlConstants, _
LookAt:=xlPart)
If Not rngFound Is Nothing Then
Set rngFoundAll = rngFound
strFirstAddress = rngFound.Address
Do
Set rngFoundAll = Union(rngFound, rngFoundAll)
Set rngFound = rngToSearch.FindNext(rngFound)
Loop Until rngFound.Address = strFirstAddress
ThisWorkbook.Names.Add Fruit, rngFoundAll.Address
End If
End Sub

--
HTH...

Jim Thomlinson


"Martin" wrote:

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