Delete Specific Range Names

  • Thread starter Thread starter Larry
  • Start date Start date
L

Larry

I want to delete specific range names. The following code
reads the range names from the 1st row
of "Formatted_Input" worksheet into an array. That part
works, but I'm not familiar enough with the "Names"
property to know how to make it delete the ranges whose
names are in the MyArray array.
When I run it I get a "type mismatch" in the "For Each
Name in MyName" statement. I'd appreciate very much a fix
and a brief explanation of what's going on. Many thanks
and Happy Holidays!

Sub DeleteSelectRanges()
Dim MyArray(1 To 256)
Dim i As Integer
Sheets("Formatted_Input").Select
For i = 1 To 200
MyArray(i) = Cells(1, i)
Next i
Names.Add Name:="MyName", RefersTo:=MyArray
For Each Name In MyName
Name.Delete
Next Name
End Sub
 
Sub DeleteSelectRanges()
Dim MyArray(1 To 256)
Dim i As Integer
Sheets("Formatted_Input").Select
For i = 1 To 200
MyArray(i) = Cells(1, i)
Next i
'Names.Add Name:="MyName", RefersTo:=MyArray
For i = 1 to 200
ActiveWorkbook.names(myarray(i)).Delete
Next
End Sub
 
-----Original Message-----
Sub DeleteSelectRanges()
Dim MyArray(1 To 256)
Dim i As Integer
Sheets("Formatted_Input").Select
For i = 1 To 200
MyArray(i) = Cells(1, i)
Next i
'Names.Add Name:="MyName", RefersTo:=MyArray
For i = 1 to 200
ActiveWorkbook.names(myarray(i)).Delete
Next
End Sub

--
Regards,
Tom Ogilvy




.
Thanks, Tom. That worked just fine. Just one other
Click to expand...
question, would it delete all range names of the same
names in other workbooks that were open if I didn't use
the ActiveWorkbook prefix on Names? Don't usually
consider that situation when I'm writing macros, but maybe
I should.
 
No, it would not check other workbooks. It would either assume the
activeworkbook or raise an error - I believe it would assume the
activeworkbook (my testing indicates it does).
 
Back
Top