Free Microsoft Excel 2013 Quick Reference

[Solved] VBA: Delete Named Ranges

I have named a certain selection of cells 'MyRange'

Is there any type of way in deleting these names that have been set for certain cells ?

Will they always be there ?


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?

Delete Excel Named Ranges By using either of the 2 Excel macros on this page you can easily delete named ranges.

Hi All,

I'm trying to check if a named range "ActiveCells" exists before deleting it in VBA but can't seem to get the syntax right, I have tried:

	VB:
	
 Else: ActiveWorkbook.Names("ActiveCells").Delete 
 
 'AND
 
If ActiveWorkbook.Names("Activecells") Is Nothing Then Resume Next Else: ActiveWorkbook.Names("ActiveCells").Delete 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Can anyone point me in the right direction? I have tried searching but I can only find threads about checking for named objects or about using the toolbars to add/delete named ranges.

Thanks!
Averil Auto Merged Post;

Sorry - I did find this one in the answers above after my post was submitted but not in my original search:

http://www.ozgrid.com/forum/showthread.php?t=76641

Thanks,
Averil

I'm trying to get VBA to name ranges based on the values in the Rows that begin and end the range. I have a spreadsheet that looks like the below, and extends 30 columns to the right. I need to a code that will find "Wholesale" and create a range that includes that row down to the first occurrence of "Monthly Ttl", and that extends 30 columns to the right. Then I want another named range starting at "Wholesale 2" and extending down to the next "monthly ttl". (there will be 6 ranges in all) Every time this report is run, the number of rows making up the ranges will change based on reservations booked against those wholesalers, so I cannot use specific cell references. Is there anyway to accomplish this in VBA?

Column A
Wholesale
data
data
data
data
Monthly ttl
Wholesale 2
data
data
data
Monthly ttl
Wholesale 3
data
data
etc....

Any help is appreciated!

Hi,

I have several old named ranges in a workbook and looked up an easy way to delete them. I have found the smae method from several sources all of which are variant of the below

	VB:
	
 
Sub DeleteNamedRanges() 
    Dim MyName As Name 
     
    For Each MyName In ActiveWorkbook.Names 
        MyName.Delete 
    Next 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines

Unfortunately when I run this macro I get the error:

"Run time error 1004"
"That name is not valid"

when I hover over MyName it appears to be the actual location of the named range rather than the name itself.

Please can you help?

Thanks,
Daf

I've looked through the OzGrid VBMacros Help File, which I think is excellent, but can not find the solution to my particular problem. I may be trying to solve the incorrect problem so I'll state my direct question and then why I'm trying to do it.

Question 1: The example code below is supposed to let you define a named range from within VBA. When I run the example code and then look in the WorkSheet, the named range is not defined. If it is possible to do this, please provide a simple function that will illustrate it.


	VB:
	
Range("C4:R9").Name = "SomeName" 
 'or
Set tempRange = Range("C4:R9") 
tempRange.Name = "SomeName" 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Question 1A. Can the pure numeric way of referencing cell ranges be used in the above functions? If so, what is the proper syntax e.g "C4:R9" --> something like (3,4:18,9) which doesn't compile

Question 2: (the reason for Question 1) Is there a way to make named ranges defined by a function available to other functions? This is what I am attempting to do in Question 1. It seems logical to me that since I can read and reference named ranges created in the worksheet and that I can "create" them in VBA, that it should be able for the named range to "travel back" to the worksheet. I plan on defining the named ranges based on results generated from earlier steps so that I can reference them when I need them in other functions. I've had no problem referencing the named ranges define while in the Worksheet but now I want to figure out my range based on the data that is generated at run time and then let other code reference those names.

Perhaps there is some more appropriate way to make the names "globally" available and persistant at least until the file is exited.

Thanks

Hi,
I have a workbook with named ranges in different worksheets.
I want to delete the names of these ranges only in Sheet1.

If I write:


	VB:
	
 ActiveWorkbook.Names 
    Name.Delete 
Next Name 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
it of course deletes everything.

But if I write:


	VB:
	
 Sheets("Sheet1").Names 
    Name.Delete 
Next Name 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
or even:


	VB:
	
Sheets("Sheet1").Activate 
For Each Name In ActiveSheet.Names 
    Name.Delete 
Next Name 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
it deletes nothing at all. And it gives no error message.

So, what do I write to delete all names in Sheet1?

Hi,

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:

Code:
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
n.Delete
End If
Next n
The error I'm getting is Run-Time error 1004, Application defined or object defined error.

Any suggestions how to fix this?

Dom

OK, I have a named range for my holidays, named Holidays.
But it is not an actual range of cells,,
I have it defined like this
={39448;39594;39633;39692;39779}

Much longer, has holidays through 2011

in a cell, I can write
=match(a1,Holidays,0)
Where A1 is a date.

Works fine.

But in VBA
MsgBox Application.Match(Range("a1"), Range("Holidays"), 0)

I get Runtime error 1004
Method Range of Object Global Failed

The same line works fine if I use a named range that refers to actual Cells.
But since my named range is a static array, it's not working.

I don't want to write the holidays in a range of cells, that's the purpose of the named range...

Is there another way I can refer to the named range Holidays in VBA ?

Thanks

excel vba
have old workbook with various named ranges scattered through different sheets. I need copy the values to a new workbook.

workbooks(oldworkbook).range("namedrange").copy destination:=workbooks(newworkbook).range("namedrange")

doesn't work.. probably because range is a property of sheets???

any patient teachers out there how can I get the info transferred over cleanly?

I have found several named ranges I used in other workbooks that I need to delete. After I delete them, save & close the workbook, they show up again in the Isert/name/define dialog after opening the book again.

The named ranges don't appear in the name box of the workbook I'm working in, so what going on?? Does the Isert/name/define dialog show all the named ranges in all of my workbooks?

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

Is there a way to delete all named ranges in a workbook ?

Regards and thank you in advance.

How do I delete all ranges named "startX" where X is the index number for a worksheet in the active workbook?

This is a non-elegant solution to the following problem. Surely someone has an elegant solution ...

I am using the following code to create an index (table of contents) of worksheets in the active workbook. Another procedure creates a new worksheet for a new customer by copying the "New Customer" worksheet with the new customer's name as the worksheet name. When this create-a-new-customer procedure runs, this ugly-but-understandable error occurs:
A formula or sheet you want to move or copy contains the name 'wrn.Q1.', which already exists on the destination worksheet. Do you want to use this version of the name?
* To use the name as defined in the destination sheet, click Yes.
* To rename the range referred to in the formula or worksheet, click No, and enter a new name in the Name Conflict dialog box.Since the person who commissioned this "simple" project is a pure novice, I'm trying to circumvent unnecessarily confusing situations. Since the informational dialog box should always be clicked Yes, I want to either find a way not to display it or remove the cause.

In any case here's code (as somewhat modified from a Web page). Apologies in advance for the lack of formatting:

Private Sub Worksheet_Activate()
Dim wBook As Workbook
Dim wSheet As Worksheet
Dim wSheetIndex As Long
Dim M As Long
M = 1
With Me
.Columns(1).ClearContents
.Cells(1, 1) = "Customer Index"
.Cells(1, 1).Name = "Index"
End With

For Each wSheet In Worksheets
ActiveSheet.Calculate
If wSheet.Name <> Me.Name Then

' Don't want an index entry for menu sheet
If wSheet.Name <> "MenuSheet" Then
' Don't want an index entry for New Customer template sheet
If wSheet.Name <> "New Customer" Then
M = M + 2
With wSheet
.Range("A1").Name = "Start" & wSheet.Index
.Hyperlinks.Add Anchor:=.Range("B1:C1"), Address:="", _
SubAddress:="Index", TextToDisplay:="Return to Index"
With .Cells.Range("B1:C1")
.Merge
.Interior.ColorIndex = 6
.Interior.Pattern = xlSolid
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.Font.Bold = True
End With
End With

Me.Hyperlinks.Add Anchor:=Me.Cells(M, 1), Address:="", _
SubAddress:="Start" & wSheet.Index, _
TextToDisplay:=wSheet.Name
End If
End If
End If
Next wSheet
ActiveSheet.Range("A1").Select
End Sub

Any help and suggestions would be greatly appreciated.

- Al (aka "Go Figure")

I'm using a count function to return a value to cell M5.

I am trying to figure out the VB code to use that value to delete a named range based on the following:

If M5 <20 delete range4
If M5 >20 but <41 delete range3
If M5 >20 but <61 delete range2
If M5 >20 but <81 delete range1

Chik wee (as Borat might say),

I'm wondering? is there any way that you can use a named range for the array in a vlookup that is done in VBA?

my named range is "Lists" and here's my code:

As ever people, thanks for looking & god bless this website or i'd be jobless!! lol

If the recorder produces...
...why then would the following fail with the goal being to remove all named ranges from a selection?

Sub
DeleteNamedRange()

Dim rngCell As Range

   For Each rngCell In Selection
      ActiveWorkbook.Names(CStr(rngCell.Name)).Delete
   Next rngCell

End Sub
I'm receiving an 'Application or Object Defined Error' at line


Hi

I hope someone can help me ou there. I'm trying to delete all named ranges
in all Worksheets, except one named "Connection Data", but I don't seem to
having any success. I have pasted my code below. If anyone can enlighten me
as to where I'm going wrong, it would be much appreciated.

Many Thanks - Grant

Sub DeleteRanges()
Dim WSh As Worksheet
Dim nm As Name

'----------------------------
'Delete All Named Ranges Except Named Ranges "In Connection Data" Workbook
'----------------------------

For Each WSh In ThisWorkbook.Worksheets
If Not WSh.Name Like "Connection Data" Then
For Each nm In ActiveWorkbook.Names
nm.Delete
Next nm
End If
Next WSh

End Sub

Hi

I've got the following list of data, and I would like to use the worksheets change event to create a named range for only the block of data matching cell B6's value.

For example, if cell B6 = "Ealing", I would like the sheet to create a named range for only Ealing's values.

Ealing G077
Ealing G078
Ealing G115
Ealing G252
Ealing G260
Ealing G303
Ealing G310

Hammersmith and Fulham G014
Hammersmith and Fulham G028
Hammersmith and Fulham G088
Hammersmith and Fulham G213
Hammersmith and Fulham G232
Hammersmith and Fulham G329
Hammersmith and Fulham G407

Hounslow 02/17
Hounslow G302
Hounslow G380
Hounslow G397
Hounslow G412

Thanks

Pete

Hello all,

I had this macro that deleted the Named Range "Table1" at the beginning of it and then redefined it (added it) and the end of the
code.

The problem is that one time through the code, it hit a snag. So after I stopped execution, Table1 is was no longer defined. The
very next time through the code, it snagged on deleting Named Range "Table1".

How would I test to see if the named range even exists before I try to delete it?

Thanks for any help anyone can provide,

Conan Kelly

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.

Is there a graceful way to handle this?

I have written VBA code which is operated every month. This code deletes several range names in a target spreadsheet, performs other actions - including extracting certain areas to different spreadsheets and then closes the spreadsheet without saving. The range names in the target spreadsheet are required for ongoing use, so can not be deleted permanently.

Is there any code that deletes all range names in one go? At present my code includes the results of recording a macro wherein I delete each range name in turn - creating script over 100 lines long.

Many thanks

Jon

Hello All,

I have a problem that's driving me nuts. I have a worksheet named 'Audit
Data' layed out like this...

Profit Center
Period 712927 713927 716927 722927
01/2005 80.00% 80.00% 80.00% 80.00%
02/2005
03/2005
04/2005
05/2005
06/2005

Then the named range ('Audit_Data') has a formula like this.....
=OFFSET('Audit Data'!A$2,0,0,COUNTA('Audit Data'!$A:$A),COUNTA('Audit
Data'!$2:$2))

When I check the named range in the worksheet, it selects the area
correctly. However, when I try to use the name range in VBA, I get an error
1004 (Method Range of Object failed). I have deleted the worksheet, thinking
corruped sheet, and added a new one. I have changed the name of the worksheet
and name range to a variety of different names and still the same error. But
one thing I found even more puzzling, I changed the formula to do the same
thing to a different worksheet of data, and the VBA worked. What am I
missing? What tree am I not seeing in this forrest?

Thank you in advance for any insight.

Dean.

i am using Excel 2000 and xp home for a platform. I am writng a vba of which
part of the automation is to first copy the info i want deleted on one sheet
and putting it into another sheet in the same workbook. Then the procedure is
to delete the rows that hae that same information on them using named ranges
to keep track of the various info. The problem comes in when i delete the
rows, the assigned addresses for the ranges are being adjusted so after a
while I end up with no range at all to work from. How do i stop this from
happening?