Finding unnamed cells in a range

7

70Bob

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
 
B

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
 
7

70Bob

That worked for me. Thank you Bob.

Bob Phillips said:
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
Click to expand...
 
Top