CODEWITHSUNDEEP
excelvba
intrigued_66
intrigued_66

Reputation: 17278

Clear named ranges on a sheet, within a specified range?

What is the best way to clear all named ranges within part of a sheet?

Something like:

Sheets("A").Range("A1:D10").ClearNames

?

Upvotes: 0

Views: 296

Answers (2)

gembird
gembird

Reputation: 14053

I do not know about ClearNames, but would do it like this. Sample:

Option Explicit

Sub DeleteNamedRanges()
  Dim targetWorksheet As Worksheet
  Dim targetRange As Range
  Dim nameObject As Name
  Dim namedRange As Range
  Dim unionedRange As Range

  Set targetWorksheet = Worksheets("MySheetName")
  Set targetRange = targetWorksheet.Range("A1:D10")

  For Each nameObject In ActiveWorkbook.Names
    Set namedRange = nameObject.refersToRange
    If (namedRange.Worksheet.Name <> targetWorksheet.Name) Then GoTo Continue

    Set unionedRange = Application.Union(namedRange, targetRange)
    If (unionedRange.Address = targetRange.Address) Then
      namedRange.Value = "" ' namedRange.Clear
    End If

Continue:
  Next nameObject
End Sub

Upvotes: 2

K_B
K_B

Reputation: 3678

In case you mean to remove the names within that area this should do the trick, please note that it removes the Name COMPLETELY not just from the target area. So a range that exists inside AND outside A1:D10 will also be deleted.

Option Explicit

Public Sub DeleteRangeNames()
    Dim wsTarget As Worksheet
    Dim rTarget As Range
    Dim nTmp As Name

    'Determine the range to clear all names from
    Set wsTarget = Worksheets("enternamehere")
    Set rTarget = wsTarget.Range("A1:D10")

    'Loop through all Names in the worksheet
    For Each nTmp In ActiveWorkbook.Names
        'Check if they overlap (if no overlap the intersection is Nothing)
        If Not (Intersect(nTmp.RefersToRange, rTarget) Is Nothing) Then
            'Delete the Name object from the workbook
            nTmp.Delete
        End If
    Next nTmp
End Sub

Upvotes: 0

Related Questions