How do I find unnamed cells within a range of cells?

I have a range of over 3000 named cells and I want to find whcih ones are
unnamed. Is this possible?

Thanks

Here is one way

Sub testAntiUnion()
Debug.Print AntiUnion(Range("$A1:$H100")).Address
AntiUnion(Range("$A1:$H100")).Select

End Sub

'-----------------------------*------------------------------*------
Function AntiUnion(UsedRange As Range) As Range
'-----------------------------*------------------------------*------
Dim saveSet
Dim rng As Range
Dim nme As Name
For Each nme In ActiveWorkbook.Names
If rng Is Nothing Then
Set rng = Range(nme.RefersTo)
Else
On Error Resume Next
Set rng = Union(rng, Range(nme.RefersTo))
On Error GoTo 0
End If
Next nme

saveSet = UsedRange.Formula
UsedRange.ClearContents
rng = 0
Set AntiUnion = UsedRange.SpecialCells(xlCellTypeBlanks)
UsedRange = saveSet
End Function


--
HTH

Bob Phillips

"70Bob" wrote in message
...
How do I find unnamed cells within a range of cells?

I have a range of over 3000 named cells and I want to find whcih ones are
unnamed. Is this possible?

Thanks

That worked for me. Thank you Bob.

"Bob Phillips" wrote:

Here is one way

Sub testAntiUnion()
Debug.Print AntiUnion(Range("$A1:$H100")).Address
AntiUnion(Range("$A1:$H100")).Select

End Sub

'-----------------------------Â*------------------------------Â*------
Function AntiUnion(UsedRange As Range) As Range
'-----------------------------Â*------------------------------Â*------
Dim saveSet
Dim rng As Range
Dim nme As Name
For Each nme In ActiveWorkbook.Names
If rng Is Nothing Then
Set rng = Range(nme.RefersTo)
Else
On Error Resume Next
Set rng = Union(rng, Range(nme.RefersTo))
On Error GoTo 0
End If
Next nme

saveSet = UsedRange.Formula
UsedRange.ClearContents
rng = 0
Set AntiUnion = UsedRange.SpecialCells(xlCellTypeBlanks)
UsedRange = saveSet
End Function


--
HTH

Bob Phillips

"70Bob" wrote in message
...
How do I find unnamed cells within a range of cells?

I have a range of over 3000 named cells and I want to find whcih ones are
unnamed. Is this possible?

Thanks

Great! It was a bit cobbled so I wasn't sure how resilient it was.

--
HTH

Bob Phillips

"70Bob" wrote in message
...
That worked for me. Thank you Bob.

"Bob Phillips" wrote:

Here is one way

Sub testAntiUnion()
Debug.Print AntiUnion(Range("$A1:$H100")).Address
AntiUnion(Range("$A1:$H100")).Select

End Sub

'-----------------------------*------------------------------*------
Function AntiUnion(UsedRange As Range) As Range
'-----------------------------*------------------------------*------
Dim saveSet
Dim rng As Range
Dim nme As Name
For Each nme In ActiveWorkbook.Names
If rng Is Nothing Then
Set rng = Range(nme.RefersTo)
Else
On Error Resume Next
Set rng = Union(rng, Range(nme.RefersTo))
On Error GoTo 0
End If
Next nme

saveSet = UsedRange.Formula
UsedRange.ClearContents
rng = 0
Set AntiUnion = UsedRange.SpecialCells(xlCellTypeBlanks)
UsedRange = saveSet
End Function


--
HTH

Bob Phillips

"70Bob" wrote in message
...
How do I find unnamed cells within a range of cells?

I have a range of over 3000 named cells and I want to find whcih ones
are
unnamed. Is this possible?

Thanks