PDA

View Full Version : [SOLVED] Delete a known named range from a known cell



DaveGib
02-05-2014, 10:05 AM
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
02-05-2014, 12:25 PM
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
02-05-2014, 01:21 PM
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
02-05-2014, 01:26 PM
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
02-06-2014, 07:40 AM
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
02-06-2014, 07:42 AM
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