Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Control Deletion of Range Names
Cheers all! I want to perform the following simple task: Check all the Range Names in a certain Workbook, and delete some o them according to certain criteria (e.g. delete a Range Name if it first 3 characters are XXX). I'm using the following Sub, which doesn't work: Sub DELETE_SOME_RANGE_NAMES() Dim nm As Name For Each nm In ActiveWorkbook.Names If Left(nm, 3) = "XXX" Then nm.Delete End If Next End Sub Using a Msgbox to check what values the Object Variable nm is gettin (MsgBox = "nm = " & nm), I noted that its value is: = << Sheet or Location ! << Address Obviously, I would rather have Variable nm as the actual name o whatever Range in order to control a string (the actual name) rathe than a location & address... Is there any way around this? Thank you very much for your time, Ale -- Alex Hatzisava ----------------------------------------------------------------------- Alex Hatzisavas's Profile: http://www.excelforum.com/member.php...fo&userid=1455 View this thread: http://www.excelforum.com/showthread.php?threadid=26229 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Control Deletion of Range Names
Hi Alex,
Try: Sub DELETE_SOME_RANGE_NAMES() Dim nm As Name Dim pos as Long Dim sStr As String For Each nm In ActiveWorkbook.Names pos = InStr(nm.Name, "!") + 1 sStr = Mid(nm.Name, pos, 255) If Left(sStr, 3) = "XXX" Then nm.Delete End If Next End Sub --- Regards, Norman "Alex Hatzisavas" wrote in message ... Cheers all! I want to perform the following simple task: Check all the Range Names in a certain Workbook, and delete some of them according to certain criteria (e.g. delete a Range Name if its first 3 characters are XXX). I'm using the following Sub, which doesn't work: Sub DELETE_SOME_RANGE_NAMES() Dim nm As Name For Each nm In ActiveWorkbook.Names If Left(nm, 3) = "XXX" Then nm.Delete End If Next End Sub Using a Msgbox to check what values the Object Variable nm is getting (MsgBox = "nm = " & nm), I noted that its value is: = << Sheet or Location ! << Address Obviously, I would rather have Variable nm as the actual name of whatever Range in order to control a string (the actual name) rather than a location & address... Is there any way around this? Thank you very much for your time, Alex -- Alex Hatzisavas ------------------------------------------------------------------------ Alex Hatzisavas's Profile: http://www.excelforum.com/member.php...o&userid=14559 View this thread: http://www.excelforum.com/showthread...hreadid=262297 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
converting email address names in a range of cells to real names | Excel Worksheet Functions | |||
COPYING FORMULA CONTAINING NAMES/RELATIVE RANGE NAMES | Excel Discussion (Misc queries) | |||
Mass deletion of block names | Excel Discussion (Misc queries) | |||
Range deletion | Excel Programming | |||
Variable Control Names | Excel Programming |