Delete a known named range from a known cell

[DaveGib]

Hi All,
I have some code that searches through a data table and puts the result in col AA. this data can, and does, contain duplicates, so with vba, I apply an Advanced filter to give a unique list and paste in col AB.
This all works well the first time, but if I rerun my query I get an error, and discovered that the advanced filter puts a named range called "Extract" in AB1.
I have some code to .clear cols AA:AB before re-running, it clears the data but not the named range
If I manually delete the named range the code then runs ok again - once!
I cant seem to find a way to delete the named range, without creating a function, which I know nothing about.
Has anyone any suggestions??
The code below is what I use for the advanced filter, - would there perhaps be another way to get the same result - I need to get a unique, sorted list in col AB from column AA - without the advanced filter.... or to be able to just delete the range

Any help or suggestions would be appreciated
Dave

 If numSubbieHouses > 1 Then ' Check if there is more than 1 house to apply adv filter ....
        Range("AA2:AA" & numSubbieHouses & "").Select
        Selection.AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Range( _
            "AB1"), Unique:=True ' remove any duplicates from col AA
        Else: Range("AA1").Select  ' ... if only 1 house, just copy and paste the single house number
            Selection.Copy
            Range("AB1").Select
            Selection.Range("AB1").PasteSpecial xlPasteValuesAndNumberFormats

[DaveGib]

I have tried the code below to try and delete the named range, but get an error...

If Cells(1, 28).Name = "Extract" Then ' check to see if range 'Extract' exists in cell AB1
            Worksheets("REPORTS").Name("Extract").Delete  ' Delete named range created by adv filter
         End If

[GTO]

I don't often used named ranges, but check the scope, as I think it belongs to the workbook maybe? Also - I think it should be .Names("NameOfRange"), like:

ThisWorkbook.Names("test").Delete

[Kenneth Hobs]

There are global names and local names. Maybe parts of this will help.

Sub RemoveLocalNames()
    Dim n As Name
    For Each n In ThisWorkbook.Names
        If InStr(1, n.Name, "!") > 1 Then
          'n.Delete
          Debug.Print "Sheet Name:", n.Name, n.RefersTo, n.RefersToLocal
          Else: Debug.Print "Workbook Name:", n.Name, n.RefersTo, n.RefersToLocal
        End If
    Next n
End Sub

Sub CellInWorkbook()
  Dim namTemp As Name
  For Each namTemp In ActiveWorkbook.Names
      If Union(namTemp.RefersToRange, Range("A1")).Address = Range("A1").Address Then
          MsgBox namTemp.Name
      End If
  Next
End Sub

Sub DelAllNames() 'Both Local and Global
    Dim n As Name
    For Each n In ThisWorkbook.Names
        n.Delete
        If InStr(1, n.Name, "!") > 1 Then
          n.Delete
          'Debug.Print "Sheet Name:", n.Name, n.RefersTo, n.RefersToLocal
          'Else: Debug.Print "Workbook Name:", n.Name, n.RefersTo, n.RefersToLocal
        End If
    Next n
End Sub

[DaveGib]

Hi Kenneth,
Thanks very much for your suggestions, - I played around with a couple of them, and have managed to get something to work ( attached below) , although I don't quite understand how ....
The code loops through the first time and picks up the name in the variable n ( there is only one name in this sheet) and then in the second loop the name gets deleted, before exiting.
The main thing is it works and the range does get deleted - THANK YOU!
Regards
Dave

    Dim n As Name
    For Each n In Worksheets("REPORTS").Names  ' Limit Search to this worksheet
        n.Delete
    Next n

[DaveGib]

Hi GTO,
Thanks very much for taking the trouble to answer my question, _ I tried your suggestion, but unfortunately I could not get a result, I did however manage to get a result by modifying Kenneth's suggestion above.
Thanks again,
Dave