|
Delete Empty or 'Broken' Named Ranges (#REF!)
This macro will delete named ranges which refer to empty or #REF! ranges. This means that if a named range has been 'broken' so that it now says "#REF!" this named range will be deleted from the workbook. This can often happen when deleting a tab with a named range or when importing data from other excel worksheets or workbooks.
Note: When you run this macro, it will delete all emtpy or 'broken' named ranges in the entire excel workbook. It will not ask you if you want specific named ranges deleted. Therefore, make sure you do not accidentally delete something which you really want to be there.
Where to install the macro: Module
Delete Empty or 'Broken' Named Ranges (#REF!)
Sub Delete_Empty_Named_References()
Dim nName As Name
For Each nName In Names
If InStr(1, nName.RefersTo, "#REF!") > 0 Then
nName.Delete
End If
Next nName
End Sub
How to Install the Macro
- Select and copy the text from within the grey box above.
- Open the Microsoft Excel file in which you would like the Macro to function.
- Press "Alt + F11" - This will open the Visual Basic Editor - Works for all Excel Versions.
Or For other ways to get there, Click Here.
For Excel Versions Prior to Excel 2007
Go to Tools > Macros > Visual Basic Editor
For Excel 2007
Go to Office Button > Excel Options > Popular > Click Show Developer tab in the Ribbon. Then go to the Developer tab on the ribbon menu and on the far left Click Visual Basic
- On the new window that opens up, go to the left side where the vertical pane is located. Locate your Excel file; it will be called VBAProject (YOUR FILE'S NAME HERE) and click this.
- If the Macro goes in a Module, Click Here, otherwise continue to Step 8.
- Go to the menu at the top of the window and click Insert > Module
- Another window should have opened within the Visual Basic Editor's window. Within this new window, paste the macro code. Make sure to paste the code underneath the last line of anything else that is in the window.
- Go to Step 8.
- If the Macro goes in the Workbook or ThisWorkbook, Click Here, otherwise continue to Step 8.
- Directly underneath your excel file called VBAProject(your file's name here), click the Microsoft Excel Objects folder icon to open that drop-down list.
- Then, at the bottom of the list that appears, double-click the ThisWorkbook text.
- A new window inside the Visual Basic Editor's window will appear. In this new window, paste the code for the macro. Make sure to paste this code underneath the last line of any other code which is already in the window.
- Go to Step 8.
- If the Macro goes in the Worksheet Code, Click Here, otherwise continue to Step 8.
- Directly underneath your excel file called VBAProject(your file's name here), click the Microsoft Excel Objects folder icon to open that drop-down list.
- Within the list that appears you will see every worksheet that is in that excel file. They will be listed as such: Sheet1(NAME OF SHEET HERE) and under that will be Sheet2(NAME OF SHEET HERE). Select the sheet in which you want the macro to run and double-click that sheet.
- A new window inside the Visual Basic Editor's window will appear. In this new window, paste the code for the macro. Make sure to paste this code underneath the last line of any other code which is already in the window.
- Repeat steps b and c for every sheet you want the macro to work in. Putting the macro in one sheet will not enable it for any other sheets in the workbook.
- Go to Step 8.
- Close the Microsoft Visual Basic Editor window and save the Excel file. When you close the Visual Basic Editor window, the regular Excel window will not close.
- You are now ready to run the macro.
Similar Helpful Excel Resources
I'm trying to build a workbook that builds a query table based on a user's entry of a parameter and have this change when they enter a new parameter.
I've built the macro so that it's structured like so
1) clear any existing data
2) get data from report
3) copy and transpose it to another sheet
4) clear original site of the report
and attached this to a button called "Go".
That works fine, but Excel is remembering the fact that I've used the query table name before and incrementing it's name with a "_n" and is consequently causing me difficulties when I try referring to the named query table.
The code I'm using to delete existing named ranges is:
For Each nName In Names
nName.Delete
Next nName
Does anyone know how to do this and ensure that when excel creates the new named range it resets the count of the name to 0?
Thanks in advance
Hi,
don't know if this question is already posted, but it's a bit complex to make a simple search in the forum.
I use a named range to provide values to a validation list.
ATM the code appears this way:
Code:
ThisWorkbook.Names.Add Name:="Object_List", RefersTo:= _
"=OFFSET(Objects!$A$2,0,0,MAX(IF(NOT(ISBLANK(Objects!$A$2:Objects!$A$1000)),ROW(Objects!$A$2:Oggetti!$A$1000),0),1))", Visible:=True
This code fills the range with all the values between A2:A1000 excluding the trailing blank cells, but picks the blank cells in between.
My question is: how can I exclude blank cells between other non blank cells?
I'm looking for a simple way to solve this that doesn't involve too much code.
here's an example:
Thanks in advance
Hi, I recently found a thread which describes how to delete all the named ranges in a workbook, I am trying to modify this slightly to only delete named ranges in the cell range or column specified:
Dim rName As Name
For Each rName In Range("A2:A10000")
rName.Delete
Next rName
End Sub
This produces an error, can anyone advise on this?
Hi All,
I had a sub that have since misplaced...
It deleted all named ranges that started with the 3 letters that I typed into an input box....
Can anyone help me here .. I have thousands to delete...
keith
Another one from my favourite spreadsheet...
I have a number of crazy defined names in my spreadsheet (1,746 to be exact). Not sure where they all came from, but I need them gone gone gone. Some of them start with squares at the front of the name and most of them refer to =#ref! or similar. I've seen a number of vba solutions to this, but none of them seem to work for me - I get "400" errors or run time errors. For reference, I'm hitting Alt F11, pasting the whole sub in, saving, then running....
Can anyone out there help?
Thanks again...
lmoon
hi there... can i modify this code to select the named ranges i wantto delete from a list?
Sub DeleteRangeNames()
Dim rName As Name
For Each rName In ActiveWorkbook.Names
rName.Delete
Next rName
End Sub
cheers
So I'm thinking this is an easy one. I ran this code that I found on this board to get a list of all the named ranges in my file:
Code:
Sub Find_bad_Names()
Dim i As Long
For i = 1 To ActiveWorkbook.Names.Count
Sheets("Sheet2").Range("A" & i) = ActiveWorkbook.Names(i).Name
Sheets("Sheet2").Range("B" & i) = "'" & ActiveWorkbook.Names(i).RefersTo
Next i
End Sub
That code worked great, I have about 4,000 named ranegs in this file, and the vast majority are ranges used in a different template that are irrelevant to this file. Luckily, all of these irrelevant named ranges names begin wth the text "IQ_".
So i tried the following code to delete named rnages that contain "IQ_" but it doesn't seem to be working. It seems to be looking at the "refersTo" value instead of the range name itself. Seems like a simple tweak but I'm not sure what to do.
Code:
Sub DeleteDeadNames()
Dim nName As Name
For Each nName In Names
If InStr(1, nName, "IQ_") > 0 Then
nName.Delete
End If
Next nName
End Sub
Gang,
I've created a bunch of named ranges and I'd like to delete a bunch of
them. Is anyone aware of an easy way to delete them en mass? The
one-at-a-time method is painfully slow when we're talking about a
couple hundred.
Thanks in advance...
Mike
I have a worksheet that I update every morning and then copy the results to a workbook that has each day's worth of data on a different sheet. Whenever I copy the sheet to the other workbook, I keep getting messages that about 30 named ranges already exist and if I want to keep the new one or old one. I cannot find these ranges. They are not there. Where could they be hidden where I can find them to delete them, because I don't use them.
Thanks.
how do you go about deleting particular named ranges? I have a macro that creates a sheet index at the front of the workbook (sourced from a MrExcel member years back). in doing so, it creates a named range on each sheet :
Code:
With wSheet
.Range("A1").Name = "Start_" & wSheet.Index
.Hyperlinks.Add Anchor:=.Range("A1"), Address:="", _
SubAddress:="Index", TextToDisplay:="Back to Sheet Index"
so in cell A1, on each sheet in the workbook, appears a hyperlink back to the Index sheet.
on the index sheet, it then anchors a hyperlink to that named range:
Code:
.Hyperlinks.Add Anchor:=WSIND.Cells(l, 1), Address:="", _
SubAddress:="Start_" & wSheet.Index, TextToDisplay:=wSheet.Name
so when you click on the sheet name on the sheet index, it takes you to cell A on that particular sheet.
another macro, fired as a close event, within the workbook reshuffles the tabs and then recreates the Sheet Index by calling the Sheet Index macro. The result is an ever increasing number of irrelevant named ranges beginning with "Start_".
unless there is a better method, I have been trying to delete the named range in cell A1 on each sheet using:
Code:
.Range("A1").Name.Delete
at the start of the With wSheet loop described above. while it doesn't bug out, it doesn't seem to do anything.
maybe there is a way to delete all named ranges beginning with "Start_"??????
love some suggestions please
|
|
