Delete Names - Error 424

[trough]

I swear I've used this code before but now I am getting Run-time error '424': Object required. I've been looking at my script and searching online and cannot figure out the issue. Please advise if you could.

Sub DelNames()

Dim mgrName As Name
Dim NameHdr() As String
Dim i As Integer

ReDim NameHdr(1 To 3)

NameHdr(1) = "Hdr1"
NameHdr(2) = "Hdr2"
NameHdr(3) = "Hdr3"

For Each mgrName In ActiveWorkbook.Names
For i = 1 To 3
If mgrName.Name = NameHdr(i) Then mgrName.Delete
Next i
Next mgrName

End Sub

 

[Trebor76]

Hi trough,

I think the issue is that the names were created in Excel 97-03 where the last column was IV. From 2007 XFD is the last column so HDR1 is actually a cell reference which cannot be a named range.

Changing the named names to "Header1", "Header2" and "Header3" (or something that is not a valid cell reference) should solve the issue.

Regards,

Robert

 

[trough]

Nope, nothing to do with Excel 97-03 suggestion but thanks. However, didn't figure out issue but did figure out I was making it way harder than I needed too. This works perfect:

Sub DelNames()

Dim NameHdr() As String
Dim i As Integer

ReDim NameHdr(1 To 3)

NameHdr(1) = "Hdr1"
NameHdr(2) = "Hdr2"
NameHdr(3) = "Hdr3"

For i = 1 To 3
ActiveWorkbook.Names(NameHdr(i)).Delete
Next i

End Sub

 

[RoryA]

Your For i = loop always loops three times, even if you have deleted the name, so any iterations after the name is deleted are referring to a non-existent object.