Deleting Range with abnormal name and invalid references 1

[sabascal]

Hi all,

Quick question:
I have a spreadsheet containing invalid ranges, I want to delete.
Range names are with invalid caracters pointing at invalid references with #HREF...
I tried to delete them from Inser>Name...Delete - Did not work

Then I tried to list them and kill them from VBA.

---
Sub Listranges()
'On Error Resume Next
Range("A:A").Delete
For i = 1 To 100
Sheets("test").Cells(i, 1).Value = Application.ActiveWorkbook.Names(i)
Next i

End Sub

Sub ClearRanges()
'On Error Resume Next
For i = 1 To 10
Application.ActiveWorkbook.Names(i).Delete
Next i

End Sub

---


When trying to delete the range I am getting an "object define error"

PS: This code is woking on fine ranges.

Any ideas?

Thanks
Seb

 

[PHV]

Have you tried something like this ?
Sheets("test").Cells(1, 1).ListNames
For Each n In ActiveWorkbook.Names
n.Delete
Next

Hope This Help, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[PaulRD]

Check out Name Manager (link below). This code is excellent for tracking names across all sheets and allows multiple selection of names with errors. I use this program on a regular basis.

http://tinyurl.com/2g3kx

Paul D
pimpin' aint easy

 

[sabascal]

Paul D,
Thanks for the link. Very usefull tool indeed.
But it doesn't work in my case. It is saying "This name is unusable" .

Any other idea?

 

[xlbo]

I have used this little sub before with success:

Sub delREFNames()
With ActiveWorkbook
For Each rn In .Names
    If InStr(1, rn.Value, "REF") <> 0 Then
        myAns = MsgBox("Range " & rn.Name & " is invalid" & vbCrLf & "Delete range?", vbYesNo, "Range Manager")
        If myAns = vbYes Then rn.Delete
    Else
    End If
Next
End With
End Sub

Rgds, Geoff

Never test the depth of water with both feet

Help us to help you by reading FAQ222-2244 before you ask a question

 

[sabascal]

Thanks -
This is basically the same I tried to do.
I don't have any problem to filter the range to delete.
I don't have any problem to delete range with valide names even if they point to #HREF.

My problem is:
- I have a range with a "invalid" name - using strange caracters. I don't know where it comes from. Let's say it's index number is 1

I am completely unable to delete it, using code that normally works on others ranges.

Question: Is there a way to clear all ranges defined in a sheet, without deleting one by one?

Thanks
-

 

[xlbo]

How have these names got onto the sheet ?? I didn't think it was physically possible to create range names with invalid characters.....

Rgds, Geoff

Never test the depth of water with both feet

Help us to help you by reading FAQ222-2244 before you ask a question

 

[sabascal]

Initially I copied a sheet from another workbook and then deleted it. This would normally result in invalid range pointing at #REF. Problem here is it also messed up the names, and as a result I manage to list those range but not rename, nor delete them.

 

[xlbo]

Can you not simply copy the data out to a new sheet ??

Rgds, Geoff

Never test the depth of water with both feet

Help us to help you by reading FAQ222-2244 before you ask a question