Finding unnamed cells in a range
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 |
All times are GMT +1. The time now is 05:44 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com