delete named ranges

d0wnt0wn

Well-known Member
Joined
Oct 28, 2002
Messages
771
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
 

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.
Code:
Sub DeleteOptionForRangeNames()
Dim i As Integer
Application.ScreenUpdating = False
On Error Resume Next
i = ActiveWorkbook.Names.Count
    For x = i To 1 Step -1
    Msg = "Do you want to Delete Named Range" & vbCrLf _
            & ActiveWorkbook.Names(x).Name & "?" & vbCrLf _
            & "" & vbCrLf _
            & "With Reference to:" & vbCrLf _
            & ActiveWorkbook.Names(x).RefersToR1C1
    Ans = MsgBox(Msg, vbYesNo + vbQuestion, "Delete Named Range?")
    If Ans = vbYes Then ActiveWorkbook.Names(x).Delete
    Next x
Sheets(ShtName).Activate
Application.ScreenUpdating = True
End Sub
 
Upvote 0
Code:
Sub DeleteOptionForRangeNames()
Dim i As Integer
Application.ScreenUpdating = False
On Error Resume Next
i = ActiveWorkbook.Names.Count
    For x = i To 1 Step -1
    Msg = "Do you want to Delete Named Range" & vbCrLf _
            & ActiveWorkbook.Names(x).Name & "?" & vbCrLf _
            & "" & vbCrLf _
            & "With Reference to:" & vbCrLf _
            & ActiveWorkbook.Names(x).RefersToR1C1
    Ans = MsgBox(Msg, vbYesNo + vbQuestion, "Delete Named Range?")
    If Ans = vbYes Then ActiveWorkbook.Names(x).Delete
    Next x
Sheets(ShtName).Activate
Application.ScreenUpdating = True
End Sub



hi there thanks for the reply... although the macro worked splendidly I need something a little more user friendly... there is too much risk in that one for deleting one by mistake.... if it could be done from a specific list that would be much better
 
Upvote 0
bump..... ok how bout... can i delete all named ranges in a workbook except certain ones???
 
Upvote 0
This code will list all your Named Ranges on a worksheet named "Temp".
Code:

Code:
Sub ListRangeNames()
Dim i As Integer, Temp As String, ShtName As String
ShtName = ActiveSheet.Name
Application.ScreenUpdating = False
On Error Resume Next
Sheets.Add.Name = "Temp"
i = ActiveWorkbook.Names.Count
    For x = i To 1 Step -1
        Sheets("Temp").[A65536].End(xlUp)(2, 1).Value = ActiveWorkbook.Names(x).Name
        Sheets("Temp").[B65536].End(xlUp)(2, 1).Value = "'" & ActiveWorkbook.Names(x).RefersToR1C1
        ActiveWorkbook.Names(x).Delete
    Next x
Sheets(ShtName).Activate
Application.ScreenUpdating = True
End Sub
This code will also delete each named range.

The next code will rebuild each named range from the Temp page.
Code:
Sub RenameThem()
Dim Rng1 As Range
Dim Rng2 As Range
Dim c As Range
Dim Var2 As Range
Sheets("Temp").Select
    Set Rng1 = Range("A1:A" & Range("A65536").End(xlUp).Row)
    For Each c In Rng1
        Var2 = c.Offset(, 1).Value
        Var2 = Right(Var2, Len(Var2))
            ActiveWorkbook.Names.Add Name:=c, RefersToR1C1:=Var2
    Next c
End Sub

Edit the Temp page first and then rebuild your Named Ranges using the "RenameThem" code.
 
Upvote 0
This code will list all your Named Ranges on a worksheet named "Temp".
Code:

Code:
Sub ListRangeNames()
Dim i As Integer, Temp As String, ShtName As String
ShtName = ActiveSheet.Name
Application.ScreenUpdating = False
On Error Resume Next
Sheets.Add.Name = "Temp"
i = ActiveWorkbook.Names.Count
    For x = i To 1 Step -1
        Sheets("Temp").[A65536].End(xlUp)(2, 1).Value = ActiveWorkbook.Names(x).Name
        Sheets("Temp").[B65536].End(xlUp)(2, 1).Value = "'" & ActiveWorkbook.Names(x).RefersToR1C1
        ActiveWorkbook.Names(x).Delete
    Next x
Sheets(ShtName).Activate
Application.ScreenUpdating = True
End Sub
This code will also delete each named range.

The next code will rebuild each named range from the Temp page.
Code:
Sub RenameThem()
Dim Rng1 As Range
Dim Rng2 As Range
Dim c As Range
Dim Var2 As Range
Sheets("Temp").Select
    Set Rng1 = Range("A1:A" & Range("A65536").End(xlUp).Row)
    For Each c In Rng1
        Var2 = c.Offset(, 1).Value
        Var2 = Right(Var2, Len(Var2))
            ActiveWorkbook.Names.Add Name:=c, RefersToR1C1:=Var2
    Next c
End Sub

Edit the Temp page first and then rebuild your Named Ranges using the "RenameThem" code.


thanks dats for the help but still not quite what I am looking for..... i have a code that lets me delete all unwanted "sheets" in a workbook except the ones that i specify

Dim i As Integer, j As Integer, shts As Integer, KeepShts
KeepShts = Array("One", "Template", "SheetNames", "main", "players")

is there no way to do the same kind of thing with named ranges?
 
Upvote 0
This code will remove all named ranges except the ones listed in the code:
Code:
Sub DeleteNamedRangesExcept()
For Each wn In ActiveWorkbook.Names
    Select Case wn.Name
        Case Is = "WedSups", "NotNeeded", "Clients"
        Case Else
        wn.Delete
    End Select
Next wn
End Sub
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,251
Members
448,556
Latest member
peterhess2002

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top