Finding unnamed cells in a range

[Guest]

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

 

[Bob Phillips]

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

 

[Guest]

That worked for me. Thank you Bob.

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

 

[Bob Phillips]

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