How to delete the contents of a named range

1
votes

I am trying to delete all of the contents of certain named ranges under certain criteria. Specifically, if the name of the range ends in "CA", then all of the contents in the cells composing that range should be cleared. This is what I have so far:

For Each Nm in ActiveWorkbook.Names
    If Right(Nm.Name, 2) = "CA" Then
        Range(Nm.Name).ClearContents
    End If
Next Nm

This produces an error:

Method 'Range' of object '_Global' failed

I've also tried to take off the .Name and just use:

Range(Nm).ClearContents

but that gives the same error. Providing some sort of workbook and sheet reference gives the error: 

Application-defined or object-defined error

Changing .ClearContents to .Clear does nothing.

I know it's finding the range because it's getting passed the if-statement.

Am I missing something silly?

EDIT: For what it's worth, the named range in question is in another workbook from where the code is written that is opened in compatibility mode.

2
excelvba
Can you check that the range you are clearing doesn't have a #REF! error in the Refers To property? It will cause this error when you try to reference it if it has. – Dave
It does not. In fact, when I debug, Nm shows that it is a valid range. – Matt Cremeens
name.name will get you the range name but you still just want to clear the named range range(nm) – Davesexcel
That is similar to the proposed solution below. I am trying it, it just takes this program a long time to run. – Matt Cremeens
@Davesexcel that did not make a difference in my case (see comment to solution below). – Matt Cremeens

2 Answers

2
votes

So, I can't be sure why this happened as the workbook I was getting the named ranges from was not one I created, but it appears as though the "RefersTo" for the named ranges in question had extra quotation marks that needed to be removed so as to refer to a legitimate range. See below

enter image description here

So I added code that replaced the quotation marks with nothing and this seemed to have resolved the issue.

For Each Nm In Names
  If Right(Nm.Name, 2) = "CA" Then
    newName = Replace(CStr(Nm), Chr(34), "")
    Range(newName).ClearContents
  End If
Next Nm

I know many of you have been looking at this and I appreciate it. Thank you.

0
votes

This works for me:

Dim nm As Name

For Each nm In Names
    If LCase(Right(nm.Name, 2)) = "ca" Then
        Range(nm).ClearContents
    End If
Next nm