Can't delete named ranges using VBA

[frankt68]

Hi!
With the help of the VBA code, I created several named ranges. The name consists of the word "Range" and the line number in which the range begins (eg "Range1", "Range44", "Range205", without ").
I can delete all of these ranges manually in the Names Manager.
However, I have trouble deleting these ranges using the VBA code belove

Sub DeleteNames()
'Update 20140314
Dim xName As Name
For Each xName In Application.ActiveWorkbook.Names
    xName.Delete
Next
End Sub

It gives me Run-time error 1004: The syntax of this name isn't correct.
d65c0210-cd74-4feb-87a0-6f91f03f8282.png

I would appreciate any advise on how to resolve this. I'm using excel 365.

Frank

[TMS]

Works for me.


Attach a sample workbook (not a picture or pasted copy). Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

[bakerman2]

How about this.

Dim xName As Name
For Each xName In Application.ActiveWorkbook.Names
    If InStr(xName.Name, "Range") > 0 Then xName.Delete
Next

[Winon]

@ bakerman2,

Your formula does not work! I have pasted it in the formula bar and it does not delete anything. It just shows the text!

[bakerman2]

I have pasted it in the formula bar

You infidel, are you on something right now ?

Tjaart, I guess you posted in the wrong thread because to my knowledge this is still VBA-code.

[frankt68]

Thanx TMS and bakerman2.


bakerman2 unfortunately, your code does not work for me.

However, after quite some time spent on searching and trying I found a solution that works.

Public Sub Delete_Names()
 Dim N As Variant
 
 If ActiveWorkbook.Names.Count < 1 Then
  MsgBox "Hidden Ranges for " & _
   ThisWorkbook.Name & _
    " cannot be found", vbInformation, ThisWorkbook.Name
 Else
 
  Dim i, j As Integer: j = ActiveWorkbook.Names.Count
  On Error Resume Next
  For Each N In ActiveWorkbook.Names
   N.Delete
   i = i + 1
  Next N
 
  MsgBox i & " Named Range(s) were Deleted out of: " & j
 
 End If
 
End Sub

The code is not mine, but I can't remember where I found it.

[rakeshkhedkar]

This code worked perfectly for me.

[TMS]

Thanks for letting us know. The only thing that's different is the error trapping. I'd still be interested in seeing a sample workbook to try and analyse what it fails on. I had even tried adding a Table and that was happily ignored.

[frankt68]

Hi TMS!

I attached the files. "Original.xlsx" is the original file, "Original after adding ranges.xlsx" is a file with added ranges using a VBA code and some modification (eg summing quantities in column J) and "Original after removing ranges.xlsx" is a file with all named rages removed, using previously mentioned code.

I'm sorry about the language in these files.

[romperstomper]

The issue is the hidden names created for backwards compatibility of new functions. You can test for that explicitly like this:

        If Not Left$(LCase$(xName.Name), 5) = "_xlfn" Then xName.Delete