Results 1 to 3 of 3
  1. #1
    Brian Roby

    Removing Name ranges in formulars

    Can anyone please tell me how to undo an "Apply Names" to the spreadsheet without having to manually change all the formulars?(there are quite a few!) I am using Excel97.
    I want to remove the names as there will be quite a few copies of each sheet in the final copy of the workbook and I want to avoid any potential conflicts at a later stage.

  2. #2
    Star Lounger
    Join Date
    Jan 2001
    Adelaide, South Australia
    Thanked 0 Times in 0 Posts

    Re: Removing Name ranges in formulars

    About the only way I know is a simple search and replace, search for the range name and replace with the cell address.

    You can have multiple versions of range names (provided they are unique within a sheet). If you reference it, Excel will use the one on the current sheet if there is one. If you want to use one on a different sheet, prefix it with the sheet name.

    For example, if I have three sheets in a workbook (sheet1, sheet2, and sheet3) and they all have a range name RangeOne on them. If I use RangeOne in a formula, then it will use the one on the current sheet. However, I can use Sheet1!RangeOne, Sheet2!RangeOne and Sheet3!RangeOne to refer to ones on other sheets (caveat: If you type in Sheet1!RangeOne and this was the first RangeOne created, Excel will change the name to Test.xls!RangeOne (assuming Test.xls is the name of the workbook).

    If that's too much info, please disregard.


  3. #3
    Platinum Lounger
    Join Date
    Dec 2000
    Queanbeyan, New South Wales, Australia
    Thanked 0 Times in 0 Posts

    Re: Removing Name ranges in formulars


    It might be done with VBA code. Maybe (just as a straing point- it's rough:

    Dim strNames() As String
    Dim strRefers() As String
    Dim xlnName As Excel.Name
    Dim i As Long
    Dim j As Long
    Dim k As Long

    i = 0
    ReDim strNames(ActiveSheet.Names.Count)
    ReDim strRefers(ActiveSheet.Names.Count)
    For Each xlnName In Excel.Names
    strNames(i) = xlnName.Name
    strRefers(i) = Mid$(xlnName.RefersTo, 2)
    i = i + 1
    For i = 1 To 3
    For j = 1 To 1
    For k = 0 To UBound(strNames)
    If InStr(Cells(i, j).Formula, strNames(k)) > 0 Then
    If MsgBox("Replace:" & vbCrLf & Cells(i, j).Formula & vbCrLf & _
    "with" & vbCrLf & _
    Replace(Cells(i, j).Formula, strNames(k), strRefers(k)) & _
    vbCrLf & "?") = vbYes Then
    End If
    End If

    The "replace" works in Excel 2000- there's code to do a replace in this thread
    Subway Belconnen- home of the Signboard to make you smile. Get (almost) daily updates- follow SubwayBelconnen on Twitter.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts