locked
excel change name of a range RRS feed

  • Question

  • Is it possible to change the name of a named range using .net code 

     

    suppose I have range(a1:a5) as named myname and I want to change it name using code how to do it

    I tried it by deleting the old name range(myname) and add my name to new range 

    but it causing problem as it delete all the cells belonging to that range

     

    myexcelObj.range(myname).name = "Name"                   'creates the named range but dont replace old one (now I                                                                                         'have two range 'names addressing to same location in excel)

    myexlObj.rnage(Name).delete() ''------------creating problem

    Thanks

    Thursday, November 11, 2010 6:22 AM

Answers

  • I did this code in excel VBA and it works

     

        Set myexcelObj = ActiveWorkbook
        myexcelObj.Names.Add Name:="Joel", RefersToR1C1:="=Sheet1!R1C1"
        myexcelObj.Names.Add Name:="Allie", RefersToR1C1:="=Sheet1!R2C1"
       
        For Each nm In myexcelObj.Names
           If nm.Name = "Joel" Then
              nm.Name = "Mark"
           End If
        Next nm


    jdweng
    • Marked as answer by bahushekh Thursday, November 11, 2010 7:41 AM
    Thursday, November 11, 2010 7:29 AM

All replies

  • I did this code in excel VBA and it works

     

        Set myexcelObj = ActiveWorkbook
        myexcelObj.Names.Add Name:="Joel", RefersToR1C1:="=Sheet1!R1C1"
        myexcelObj.Names.Add Name:="Allie", RefersToR1C1:="=Sheet1!R2C1"
       
        For Each nm In myexcelObj.Names
           If nm.Name = "Joel" Then
              nm.Name = "Mark"
           End If
        Next nm


    jdweng
    • Marked as answer by bahushekh Thursday, November 11, 2010 7:41 AM
    Thursday, November 11, 2010 7:29 AM
  • If I  understand, you want to rename a Name. You need to make a new name with the same RefersTo details as the old name. Then you need to find all formulas that contain the old name and replace with the new name. That may be straightforward or difficult depending various issues. You may find it simpler and more reliable to try the NameManager addin which can handle of that, by Jan Karel Pieterse and Charles Williams from either of their sites -

    http://www.jkp-ads.com/OfficeMarketPlaceNM-EN.asp
    http://www.decisionmodels.com/downloads.htm

    Regards,
    Peter Thornton

    Thursday, November 11, 2010 10:15 AM