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
|