CODEWITHSUNDEEP
excelvba
Matt Cremeens
Matt Cremeens

Reputation: 5151

How to delete the contents of a named range

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.

Upvotes: 1

Views: 12906

Answers (2)

Matt Cremeens
Matt Cremeens

Reputation: 5151

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.

Upvotes: 2

paul bica
paul bica

Reputation: 10705

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

Upvotes: 0

Related Questions