Excel 2003 :: VB Code To Delete ALL Named Ranges

Mar 22, 2012

I have a spreadsheet, but it came from another file using the detach sheet method. It has therefore taken with it, all the named ranges that are now superfluous.

It seems a bit long-winded to delete these named ranges one by one manually. Would there be an easy to understand script that would delete them all, so I can start with a "clean sheet".

I'm using Excel 2003.

View 5 Replies


Delete Named Ranges

Aug 26, 2008

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.

View 9 Replies View Related

VBA To Delete External Named Ranges

May 1, 2012

I am attempting to delete external named ranges after a sheet is copied out of a source workbook (the template). I have tried both the following codes, but neither one does the trick. The code is in ThisWorkbook (not a module), and I have tried it with the code both in the template and the new doc. The new doc is where I want the names to be deleted (there are no #REF names in the template).

Deletes named ranges based on the reference to the source doc:


Sub DelRanges()
Dim nName As Name

For Each nName In Names
If InStr(1, nName.RefersTo, "='T:Fin_") > 0 Then
End If
Next nName
End Sub

Deletes named ranges based on a #REF in the value of the range (this is my preferred method):


Sub DeadRanges()
Dim nName As Name

For Each nName In Names
If InStr(1, nName.Value, "#REF") Then
End If
Next nName
End Sub

View 1 Replies View Related

2007 Delete My Named Ranges

Jul 7, 2009

Doesn't happen all the time, and generally happens when a macro is run.

View 9 Replies View Related

Delete All Named Ranges With Exceptions

Oct 11, 2006

Basically I have the code to Delete All named ranges in active workbook, but I need it to skip over two named ranges called Categories and Length. Is there a way to adjust this to delete all named ranges in active workbook except a named range Categories and another called Length

Dim rName As Name
For Each rName In ActiveWorkbook.Names
Next rName

View 2 Replies View Related

Delete Hidden Named Ranges

Oct 6, 2007

I'm trying to delete all the Named Ranges in my excel document, but it appears there are some hidden ranges, and I can't delete them! How do you delete a range that is hidden? If I try to delete it with my code it says "That name is not valid." So I created a message box to see what the name of the range is.....and it's like 30 letters long! It starts like this..."={#N/A,#N/A, TRUE "Summary....." I woud like to attach my Excel file but it's too large. Below is my code

Sub Remove_Hidden_Names()
' Dimension variables.
Dim xName As Variant
' Loop once for each name in the workbook.
For Each xName In ActiveWorkbook.Names
If xName.Visible = True Then
MsgBox (xName)
End If
Next xName
End Sub

View 5 Replies View Related

Delete Named Ranges From Individual Worksheet

Jun 16, 2008

I was after a bit of code to delete the range names on a particular worksheet I thought it couldn't be that hard but have only been able to find this in the archives, unfortunately I get an error when trying to execute it:

Sub Delete_My_Named_Ranges()
Dim n As Name
Dim Sht As String
' Put in name of sheet where the range is located
Sht = "Org Lookups"
For Each n In ThisWorkbook.Names
If n.RefersToRange.Worksheet.Name = Sht Then
End If
Next n
The error I'm getting is Run-Time error 1004, Application defined or object defined error.

View 9 Replies View Related

Delete Rows Without Named Ranges Failing

Apr 3, 2008

I have a dynamic named range in my worksheet:

Range Name = AssignDt
RefersTo: =OFFSET('Raw Data'!$W$2,0,0, COUNTA('Raw Data'!$A:$A)-1,1)

I perform a routine in VBA which deletes unwanted rows of data from my worksheet. The problem occurs if row 2 happens to be one of those rows. It not only deletes Row 2, but it also deletes my Named Range.

View 7 Replies View Related

Create Dynamic Named Ranges With Code

Oct 13, 2007

I am employing code to label dynamic ranges that takes the form of,

ActiveWorkbook.Names.Add Name:="dms", RefersToR1C1:="=OFFSET(DMS!R10C5,0,0, COUNTA(DMS!C5),COUNTA(DMS!R10))"
ActiveWorkbook.Names.Add Name:="dms_j", RefersToR1C1:="=OFFSET(DMS!R11C10,0,0,MATCH("" * "",DMS!C10,-1),1)"
ActiveWorkbook.Names.Add Name:="dms_p", RefersToR1C1:="=OFFSET(DMS!R11C16,0,0,MATCH("" * "",DMS!C16,-1),1)"
ActiveWorkbook.Names.Add Name:="dms_r", RefersToR1C1:="=OFFSET(DMS!R11C18,0,0,MATCH("" * "",DMS!C18,-1),1)"
ActiveWorkbook.Names.Add Name:="dms_t", RefersToR1C1:="=OFFSET(DMS!R11C20,0,0,MATCH("" * "",DMS!C20,-1),1)"

The dynamic ranges are getting entered but the problem is that the range "dms" overshoots by six cells into blank cells at the bottom of the table, and the rest of the ranges overshoot by ten cells into blank cell area. I have deleted, cleared and destroyed everything around the table and re-sized the active area to no avail.

View 9 Replies View Related

How To Use Named Ranges In VBA Code Using Columns And Variation On Range

Mar 17, 2014

I am trying to clean up my code by using named ranges so that it will still work if/when others add columns or rows to the spreadsheet. These should be pretty easy solutions for most of you but I can't seem to find any answers online.

Here are 2 examples of the code I am trying to update:

I was thinking that replacing "A" with "namedRange" would work but I suppose that's not how the Columns application works. How can I modify the code below to work with a named range instead of the fixed column "A"?


For the following I would have thought that replacing "AU" with "namedRange" would do the trick as that's how things have worked for me in the past using the Range application. Unfortunately I get an error when I make the change.

[Code] .....

View 2 Replies View Related

Excel 2003 :: VBA To Add Worksheets Named For Items In Range?

Jan 17, 2012

I some code that reduces a long list of dates from one source of data (in its own worksheet) down to a unique list of dates (on another worksheet). I need to create a new worksheet for each date in the range (named for the date), and then allocate each line item for that date in the source data into its repsective worksheet.

I'm using Excel 2003.

View 3 Replies View Related

Excel 2003 :: When Inserting New Row SUM Ranges Do Not Update

Jun 3, 2013

I am using Excel 2003.

I have a column. D3-D7 have numbers in them and I want to add them. I put a formula in D8 to SUM all the Cells From D3-D7. No problems there.

When I insert a row above D8 the range for the SUM is not expanding, and if I do put info in the cell a box comes up asking if I want to update the formula.

Is there a way to insert a new row and have it be included in a range?

View 5 Replies View Related

Non-Continuous Range In Event Code: Show A UserForm When A Cell In 1 Of 31 Named Ranges Is Selected

Nov 7, 2006

I am using the following Selection_Change Event to show a UserForm when a cell in 1 of 31 named ranges is selected.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
Dim i As Long

For i = 1 To 31
If Not Intersect(Target, Range("StatPost" & i)) Is Nothing And Target.Value = "" Then
If Target.Offset(0, -8).Value = "" Or Target.Offset(0, -7).Value = "" Or Target.Offset(0, -6).Value = "" Or Target.Offset(0, -5).Value = "" Or Target.Offset(0, -3).Value = "" Or Target.Offset(0, -2).Value = "" Or Target.Offset(0, -1).Value = "" Then....................

View 3 Replies View Related

Connecting Excel To Access Queries - Named Ranges

Jan 11, 2014

So I have an excel workbook that is connected to an Access database I use. When the workbook opens, it refreshes the data from a couple queries in Access. However, every time it refreshes the data, it modifies several named ranges pulling from areas of those tables.

Example: $A$2:$A$50000 becomes $A$2:$A$50002 if there are two new lines entered into the table.

Because of this, some of the lookups and arrays I use are mismatching and giving errors. How to prevent the ranges from updating when the table refreshes?

View 3 Replies View Related

Excel 2003 :: VBA - Adding Named Template Worksheets Based On Single Cell Value

Apr 24, 2012

I'm trying to make a macro in Excel 2003 to create x amount of named worksheets that are a copy of a different worksheet.

1. I have a main worksheet that will have a number manually entered into a cell (lets say A1);
2. I have a 'template' worksheet;
3. I'll assign the macro to a button on the main worksheet

If I enter '10' into cell A1 of the main worksheet, I'd like to click the button and have Excel create 10 copies of the template worksheet. These new worksheets should all share the same name with a number after them (ex: banana 1, banana 2..... banana 10).

View 2 Replies View Related

Define Only 2 Named Ranges From List Of Named Ranges

Apr 28, 2014

I have written this macro to convert into a csv file to run for all defined named ranges in the activesheet. It run jst perfect when I hit SAVE button and it creates that many different CSV files for each named range.

However I am trying to use same macro in the another file and the problem I am facing is there a lot more named ranges and I want to run the macro for only selected NAMED RANGE. In this case 2 Named Range / 24 Named range.

What part of code do I need to change and to what to make it work for just 2 named ranges ?

View 6 Replies View Related

Excel 2003 :: Calculating Number Of Overlapping Days With Several Date Ranges?

Jun 25, 2010

I am having difficulty calculating the number of total overlapping days between several date ranges (6 of them to be precise)

I am using Excel 2003 and my data looks like this:

Sent Out (A) - Received (B)
01/06/2010 13:00 - 30/06/2010 13:00
02/06/2010 13:00 - 16/06/2010 13:00
09/06/2010 13:00 - 10/06/2010 13:00
21/06/2010 13:00 - 25/06/2010 9:44
23/06/2010 13:00 - 25/06/2010 10:56
23/06/2010 13:00 - 29/06/2010 13:00

I really can't wrap my head around the idea of calculating overlap (in network days) between six date ranges and was wondering if any of you would have a solution to this problem.

View 10 Replies View Related

Excel 2010 :: Intersection Of Named Ranges (same Worksheet) Doesn't Work?

Aug 19, 2013

I'm trying to make a range selection in a pivot table as per the snapshot below:


The values I am trying to select are the ones that are both red and italic. (Mobile, Other Fix, Fix) from CBU_NA.

I've searched and found a way to select both the rows for CBU_NA category and also the column categ2.

However when I try to make an intersection out of the 2 ranges I receive an error. I'm using Excel 2010 on windows 7.Below the code I am using:

Sub FCST()
Dim r1, r2, r3 As Range

r1 = pt.PivotFields("categ2").DataRange.Select
Selection.Font.Italic = True[code]...

View 2 Replies View Related

Excel 2003 :: Dynamic Named Range Of Non-contiguous Cells Suitable For Chart Series Reference

Nov 11, 2012

I am running Excel 2003 on a Win7 system.

Here is my situation:

Each of my data sets spans roughly 75 columns by 250 rows at present, but this could expand. The first 7 rows contain metadata. Columns 2-25 or so contain the raw data, from which everything to the right is calculated. The data sets have most columns in common, but not necessarily all.

In order to tease out the most meaningful information from my data, I frequently sort all or part of it based on varying criteria. When I find a useful sorting criterion, I create a new column with a header that describes the criterion and populate it with a formula that returns a 1 if the condition of interest is met for that row, or a 0 if it is not. For example, if I am doing this in column AA, I might enter


and copy it down to the end of the data. The resulting vector of 1s and 0s quickly re-identify data that meets that criterion even after subsequent resorting. It also makes locating data that meets multiple sorting criteria extremely simple. Essentially, I create a truth table.

Cell $AA$4 in the above example contains a "comparator" value I might wish to change at some point, which would change the subset of data the condition selects for.

Here's the first hard part:

For each data set, I need the ability to generate meaningful plots that includes separate series based on the criteria I have described. However, I also need to retain the ability to resort the data or change the comparator value without disrupting these plots. In other words, the plots must NOT change when the order of the data is changed, but MUST change to display the appropriate data when the comparator changes.

Here's the 2nd hard part:

Once I have this working for one data set, I need to be able to port it to other data sets (which are contained in other workbooks), so that I can compare equivalent plots from each. I also need to minimize the number of manual steps involved in doing so, to avoid human errors and excessive time consumption.

The only other possible complication I can think of at the moment is that, to this point, I have been inserting blank rows to isolate subsets that I do not wish to perform further sorting on from each other.

Right now I am angling toward VBA code that loops through the entire data set to generate base dynamic ranges using the column header row (row 1) as the names, and the entire column of data for the rangeloops through the truth table columns to generate "branch" row ranges for each of the sorting conditions,loops through the entire data set one more time to create "branch" ranges for each of the base ranges.

I could generate some code to accomplish a one-off solution for a given configuration of a single data set (provided there is not a list length limit in a chart series that I'd be violating)...but without a dynamic named range, I don't know how to get to something that would update appropriately. So in essence, I am still stuck at the dynamic range part of this.

View 1 Replies View Related

Excel 2003 :: Calculating Number Of Overlapping Days With Several Date Ranges And Conditions

Jan 6, 2014

I am having difficulty calculating the number of total overlapping days between several date ranges for each item in another sheet which has unique items

I am using Excel 2003 and my data looks like this:

Sheet-1 Sheet-2
Item - Sent Out (A) - Received (B) Unique Item Number of days excluding overlap days
1234 01/06/2010 - 30/06/2010 1234 -
4321 02/06/2010 - 16/06/2010 4321 -
1234 09/06/2010 - 10/06/2010
4321 21/06/2010 - 25/06/2010
1234 23/06/2010 - 25/06/2010
4321 23/06/2010 - 29/06/2010

I have used the below formula found from the earlier post but need to add a condition calculating the days for each item.


View 6 Replies View Related

Excel 2003 :: Use Find And Replace To Delete Word?

Dec 1, 2005

I'm looking to simply delete certain words that are in cells, without having to delete rows, columns or the entire cell. I can use 'find and replace' and replace the particular word with a space, but I really want to delete the word. I'm using both Excel 2003 and 2000.

View 4 Replies View Related

Excel 2003 :: Data Validation - Disable Delete Key

Mar 31, 2010

I'm using Excel 2003

Cell D7 - data validation reads as such: It is a List and the source is I6:I7. I have In-Cell dropdown selected. Ignore Blank is not checked.

I now password protect the sheet and only D7 is unlocked. real simple, the person using it can only use D7 and select one of two in the drop down menu.

The problem is, if the user clicks on D7 and hits delete, the cell goes blank. How can I avoid this?

View 8 Replies View Related

Excel 2003 :: Macro To Delete Rows In A Table

May 19, 2013

a VBA problem I have and I do not know how to solve. I use Excel 2003.

I have a workbook with a table; all cells except the table area are locked so at users cannot add any data. The table is on D2: H?. The workbook is password protected (users can only "Select unlocked cells", "Format cells" and "Sort"), and for that reason there is a "Add Rows" macro button so they can add more rows if necessary. It works well.

There is also a "Delete Rows" button, but it does not work as I want it to do. If the table is for example on D2:H4, and I select a cell on row #3 (ex. D3, or E3, or H3) and click on the button it deletes row #3, if I click on the button again it deletes row #4, and if I click on the button again it also deletes row #5 and so on, but it should not do it because all rows below row #4 are locked and should not be deleted.

I found on the Internet a code that I modified, but I do not get it to work. Below are the two macros.

My original macro (it deletes even locked rows):

Sub DeleteRows()
ActiveSheet.Unprotect Password:="123"
ActiveSheet.Protect Password:="123", DrawingObjects:=True, Contents:=True, Scenarios:=True _
, AllowFormattingCells:=True, AllowSorting:=True
End Sub

The macro found and modified (the original rows are commented):

Sub DelRows_2()
Dim rngDelete As Range
Do While rngDelete Is Nothing
On Error Resume Next


View 2 Replies View Related

Excel 2003 :: Delete Duplicates Based On Value In One Column

Jun 5, 2014

Excel 2003 - delete duplicates based on value in one coloumn.

very simple but how!i want the whole row deleted where there are duplicates in one column but ONLY the duplicates, so, if they're 3 duplicates in the column i want 2 deleted leaving 1 unique record.

View 4 Replies View Related

Named Ranges On Multiple Sheets With The Same Named Range & I Cant Figure Out How To Do This

Jun 2, 2006

I need to create a named range on multiple sheets with the same named range & i cant figure out how to do this. EG :- I want to create a named range called "_SubUnitRows" on sheet1 starting from "A1:A50" & other named range again called "_SubUnitRows" on Sheet2 starting from "A1:A25" ...

View 2 Replies View Related

Excel 2007 :: Find Certain Named Columns And Delete Them

Jun 14, 2013

Macro code to find several column names in row 1 (which can be anywhere in the work sheet) and delete them e.g. date, month, code, amount etc.......

Excel version 2007

View 4 Replies View Related

Excel 2003 :: Userform - Ok Button To Delete Data When Clicked On In Combobox

May 8, 2012

I have a userform that has a combobox in it that also has an OK button and a Cancel Button. Need getting the combobox to pull in the correct list? The data for this list will be in cells A2 through A16. But not all 15 lines will always have data in them so I'd like to only show the ones with data. Then next issue i have is I would like the OK button on this userform to actually erase the data the user has clikced on in the combobox. It's a list of kids that the userform is asking which one needs to be deleted. So it might be the kid in cell A9. so when the user clicks on that kid in the combobox form a9 and hits ok I'd like to have all the kids below A9 move up one row.

Windows XP
excel 2003

View 6 Replies View Related

Combine Two Named Ranges Into 3rd Named Range

Mar 14, 2013

Merge two columns into one list in excel

I would like to combine List1 and List2 into a 3rd named range called List3. I was wondering if this were possible without using any additional cells/columns (i.e. I don't want to use Column C like in the example shown in the link above).

Here's the formula from the example:



I've played around with it, but could not come with any that worked.

View 3 Replies View Related

Excel 2003 :: VBA Code That Sorts And Deletes Data

Jan 19, 2012

Below is an extensive macro that basically writes data from a user's template to a certain data sheet (in the form of rows) depending on their "service group." There have been 3 separate instances of a user saving data and instead of adding rows onto the bottom of the entire list of data, the sheet only has their data and no headers/filters. I believe it is somehow deleting all the previously written data that was on that sheet.

I should also mention that this is a shared workbook. I specifically created separate sheets for each "service group" to eliminate the possibility of users overwriting each other's data (Perviously 60+ users were saving to the same sheet and running into conflicting changes errors. Now the max users writing to one sheet is 10-12). Since it is not occurring every time, I am not sure where the error is. Could it possibly be in the sort or delete sections in bold below?

I am using Excel 2003.

Sub SaveData_Test()
' Procedure : SaveData_Test
' Author : Julie/Cecil
' Date : Fri, 1/13/12
' Purpose : Modified Julies code to capture date entries
' associated with hours logged per category.
' Check notes attached to "sOp" string comment
' for additional details.

[Code] ..........

View 5 Replies View Related

Excel 2003 :: Runtime Error On VBA Protected Code

Apr 24, 2012

Why am I getting a runtime error on the following code:

If Target.Address = "$E$5" And Target.Value "" Then
If Sheets("Sheet5").Range("F5").Value = "" Then
Sheets("Sheet5").Range("F5").PasteSpecial xlPasteValues
Sheets("Sheet5").Cells(Rows.Count, "F").End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
End If
End If

When I select only one cell it's ok but when I select multiple cell to delete it sends me a run-time error '13'.

Excel 2003, sheet is protected.

View 2 Replies View Related

Copyrights 2005-15 www.BigResource.com, All rights reserved