Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete range names within a selection
I've been searching extensively and can't seem to find something that
works, so I will try here. I need to develop vba code that will delete all cell names within a selected range. The selected range will include cells that are named and cells that are not named. Any help will be greatly appreciated. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete range names within a selection
hdf presented the following explanation :
I've been searching extensively and can't seem to find something that works, so I will try here. I need to develop vba code that will delete all cell names within a selected range. The selected range will include cells that are named and cells that are not named. Any help will be greatly appreciated. Have a look at the Intersect() function in VBA help. Example... <aircode Dim rngname As Variant ' For Each rngname In ActiveWorkbook.Names '//use for global scope For Each rngname In ActiveSheet.Names '//use for local scope If Not Intersect(Selection, Range(rngname)) Is Nothing Then '..delete the name ' ActiveWorkbook.Names(rngname).Delete '//use for global scope ActiveSheet.Names(rngname).Delete '//use for local scope End If Next 'rngname ...where this will work if all or part of a named range falls within the selection. -- Garry Free usenet access at http://www.eternal-september.org ClassicVB Users Regroup! comp.lang.basic.visual.misc microsoft.public.vb.general.discussion |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete range names within a selection
Oops! See the corrections below...
Example... <aircode Dim rngname As Variant ' For Each rngname In ActiveWorkbook.Names '//use for global scope For Each rngname In ActiveSheet.Names '//use for local scope If Not Intersect(Selection, Range(rngname)) Is Nothing Then '..delete the name ' ActiveWorkbook.Names(rngname.Name).Delete '//for global scope ActiveSheet.Names(rngname.Name).Delete '//for local scope End If Next 'rngname ..where this will work if all or part of a named range falls within the selection. -- Garry Free usenet access at http://www.eternal-september.org ClassicVB Users Regroup! comp.lang.basic.visual.misc microsoft.public.vb.general.discussion |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete range names within a selection
Garry,
Thanks for the help. I was trying your first version and couldn't get it to work, thanks for correcting it. I will now try and work with the revised version. HDF |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete range names within a selection
Well, I have not been able to get Garry's recommendations to work. I
am clearly not setting it up with all of the correct parameters. However, I did find some code in another thread and now I have the problem that it works just fine when testing it on a newly created workbook, but for the life of me I can't get the exact same code to work in my existing workbook. I keep getting a 1004 Run-time error and it stops at the following line of code: " If WithinRange(myCell, myName.RefersToRange) Then" Here is the code: Sub DeleteNames() Dim myRange As Range Dim myCell As Range Dim myName As Name Set myRange = Range("TESTRANGE") For Each myCell In myRange.Cells For Each myName In ThisWorkbook.Names If WithinRange(myCell, myName.RefersToRange) Then myName.Delete End If Next myName Next myCell End Sub ------- Function WithinRange(SmallRng, BigRng) As Boolean ' Returns True if smallrng is a subset of Bigrng WithinRange = False If SmallRng.Parent.Name = BigRng.Parent.Name Then If Union(SmallRng, BigRng).Address = BigRng.Address Then WithinRange = True End If End If End Function |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete range names within a selection
hdf used his keyboard to write :
Well, I have not been able to get Garry's recommendations to work. I am clearly not setting it up with all of the correct parameters All the correct parameters are there. I tested this with both local scope named ranges and global scope named ranges. It worked perfectly for me and so the only reason it may not be working for you is because you're running it as posted, but your named ranges are global scope NOT local scope. In this case, comment out the lines that act on ActiveSheet and uncomment the lines that act on ActiveWorkbook. (Commented lines are the ones prefixed with an apostrophe. Remove the apostrophe from the global scope line and prefix the the local scope line with an apostrophe!) -- Garry Free usenet access at http://www.eternal-september.org ClassicVB Users Regroup! comp.lang.basic.visual.misc microsoft.public.vb.general.discussion |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete range names within a selection
Note that there are 2 lines each for global/local scope names. You must
change both as shown here... Dim rngname As Variant For Each rngname In ActiveWorkbook.Names '//use for global scope ' For Each rngname In ActiveSheet.Names '//use for local scope If Not Intersect(Selection, Range(rngname)) Is Nothing Then '..delete the name ActiveWorkbook.Names(rngname.Name).Delete '//for global scope ' ActiveSheet.Names(rngname.Name).Delete '//for local scope End If Next 'rngname -- Garry Free usenet access at http://www.eternal-september.org ClassicVB Users Regroup! comp.lang.basic.visual.misc microsoft.public.vb.general.discussion |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete range names within a selection
GS expressed precisely :
Note that there are 2 lines each for global/local scope names. You must change both as shown here... Sub DeleteIntersectinNames() Dim rngname As Variant For Each rngname In ActiveWorkbook.Names '//use for global scope ' For Each rngname In ActiveSheet.Names '//use for local scope If Not Intersect(Selection, Range(rngname)) Is Nothing Then '..delete the name ActiveWorkbook.Names(rngname.Name).Delete '//for global scope ' ActiveSheet.Names(rngname.Name).Delete '//for local scope End If Next 'rngname End Sub It just occurred to me that you may not have put the code inside a procedure and so the above has been edited to demonstrate how to implement it. (If your range names are indeed local scope then switch the apostophes around to the original post) -- Garry Free usenet access at http://www.eternal-september.org ClassicVB Users Regroup! comp.lang.basic.visual.misc microsoft.public.vb.general.discussion |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete range names within a selection
Gary,
Thanks for your inputs. Indeed I was over thinking and was reading into your code more than I should have. Your code works just fine when I test it on a new workbook. However, I am having the same problem with your code as I do with the much more complex code I mentioned above - it will not work in the workbook for which I actually need it. I gives me a 1004 run time error if I set it to run at the ActiveWorkbook.Names level and if I set it at the ActiveSheet.Names level it doesn't do anything. It runs through the code, does not throw off any errors, but also does not erase any names within the selected range. Neither the WB or WS are protected. Any ideas on what might be causing this behaviour? Thanks for your help and patience. Hector |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete range names within a selection
I should provide some additional information on my problem.
I realize now that the Worksheet level version doesn't do anything because the names I am trying to erase are Workbook level. When running the workbook version I get a run-time error. The error is a 1004 run-time error that says, specifically, "Method 'Range' of object '_Global' failed." When I press the "Debug" option it highlights the following part of Gary's code: If Not Intersect(Selection, Range(rngname)) Is Nothing Then If I use the exact same code on a new workbook created to test it, it works as intended and erases the range names located within the selected area. I have ensured none of the worksheets nor the workbook are locked in any way. I have also unlocked all cells to see if that would do the trick, no luck either. I don't know what else to try. |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete range names within a selection
on 4/29/2012, hdf supposed :
I should provide some additional information on my problem. I realize now that the Worksheet level version doesn't do anything because the names I am trying to erase are Workbook level. When running the workbook version I get a run-time error. The error is a 1004 run-time error that says, specifically, "Method 'Range' of object '_Global' failed." When I press the "Debug" option it highlights the following part of Gary's code: If Not Intersect(Selection, Range(rngname)) Is Nothing Then If I use the exact same code on a new workbook created to test it, it works as intended and erases the range names located within the selected area. I have ensured none of the worksheets nor the workbook are locked in any way. I have also unlocked all cells to see if that would do the trick, no luck either. I don't know what else to try. For the code to work in any workbook, it must be stored in a standard module and run from the Excel window via the Macros dialog. Optionally, you can run it from the VBE -OR- store it in a standard module in PERSONAL.XLS. I suspect that you're trying to run the code behind a sheet module and so when you select that it activates that workbook's worksheet. The code will do nothing if no ranges have defined names, or the defined names are outside the scope for the line of code running. -- Garry Free usenet access at http://www.eternal-september.org ClassicVB Users Regroup! comp.lang.basic.visual.misc microsoft.public.vb.general.discussion |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete range names within a selection
On Apr 29, 7:23*pm, GS wrote:
on 4/29/2012, hdf supposed : I should provide some additional information on my problem. I realize now that the Worksheet level version doesn't do anything because thenamesI am trying to erase are Workbook level. When running the workbook version I get a run-time error. *The error is a 1004 run-time error that says, specifically, "Method 'Range' of object '_Global' failed." When I press the "Debug" option it highlights the following part of Gary's code: * * *If Not Intersect(Selection, Range(rngname)) Is Nothing Then If I use the exact same code on a *new workbook created to test it, it works as intended and erases the rangenameslocated within the selected area. I have ensured none of the worksheets nor the workbook are locked in any way. *I have also unlocked all cells to see if that would do the trick, no luck either. I don't know what else to try. For the code to work in any workbook, it must be stored in a standard module and run from the Excel window via the Macros dialog. Optionally, you can run it from the VBE -OR- store it in a standard module in PERSONAL.XLS. I suspect that you're trying to run the code behind a sheet module and so when you select that it activates that workbook's worksheet. The code will do nothing if no ranges have definednames, or the definednamesare outside the scope for the line of code running. -- Garry Free usenet access athttp://www.eternal-september.org ClassicVB Users Regroup! * * comp.lang.basic.visual.misc * * microsoft.public.vb.general.discussion Thanks for the follow up. The code is in a standard module. I have discovered that if I use the code on a workbook with more than one WS and both worksheets have names in them, it crashes. I started with a fresh WB, named four consecutive cells in each of two WS (different names for each cell). Then I placed a button linked to the macro in each WS. I then select half of the named cells in either one of the two WS and run the macro. It crashes. If I start with a fresh WB and only name cells on one WS, select some of them and run the macro, it works as intended. Is there any reason why it would only work on a single WS workbook? |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete range names within a selection
hdf wrote on 4/29/2012 :
Thanks for the follow up. The code is in a standard module. I have discovered that if I use the code on a workbook with more than one WS and both worksheets have names in them, it crashes. I started with a fresh WB, named four consecutive cells in each of two WS (different names for each cell). Then I placed a button linked to the macro in each WS. I then select half of the named cells in either one of the two WS and run the macro. It crashes. If I start with a fresh WB and only name cells on one WS, select some of them and run the macro, it works as intended. Is there any reason why it would only work on a single WS workbook? It works for the active sheet of whichever workbook is active at the time the code runs. It doesn't matter how many sheets are in the workbook because only 1 sheet can be active at any time. What's important to make it work is whether the names are defined with local or global scope. To make this easier for you, I've created 2 separate procedures so you can choose the scope befor you run the code... Sub DeleteGlobalScopeNames() Dim rngname As Variant For Each rngname In ActiveWorkbook.Names If Not Intersect(Selection, Range(rngname)) Is Nothing Then '..delete the name ActiveWorkbook.Names(rngname.Name).Delete End If Next 'rngname End Sub Sub DeleteLocalScopeNames() Dim rngname As Variant For Each rngname In ActiveSheet.Names If Not Intersect(Selection, Range(rngname)) Is Nothing Then '..delete the name ActiveSheet.Names(rngname.Name).Delete End If Next 'rngname End Sub I suggest you put this in PERSONAL.XLS so it's always available whenever you have Excel open. If your PERSONAL.XLS file is not open (in the VBE Explorer pane) then it doesn't exist yet. You can create one by recording a macro and choosing PERSONAL.XLS during the process. You don't actually have to record anything so just click 'Stop recording', delete the empty macro in 'Module1', then paste these subs into that module. To run the macros from the Excel UI, open the 'Macros...' dialog: In Ribbon versions of Excel it can be found on the 'Developer' tab. In earlier versions of Excel it can be accessed via the 'Tools' menu. -- Garry Free usenet access at http://www.eternal-september.org ClassicVB Users Regroup! comp.lang.basic.visual.misc microsoft.public.vb.general.discussion |
#14
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete range names within a selection
You can open the macros dialog via the keyboard combo Alt+F8 in all
versions of Excel... -- Garry Free usenet access at http://www.eternal-september.org ClassicVB Users Regroup! comp.lang.basic.visual.misc microsoft.public.vb.general.discussion |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to Delete blanks between a range and populate only the names inthe given range | Excel Discussion (Misc queries) | |||
Delete all Range Names Q | Excel Programming | |||
Including ActiveX Controls in Names Range Selection | Excel Programming | |||
Delete all Range Names Except for 1 Q | Excel Programming | |||
delete non-used range names | Excel Programming |