none
VBA code to convert all excel name ranges to upper case RRS feed

  • Question

  • Hi All,

    I have nearly 500 excel name ranges in lower case created in excel. Is it possible to convert them to upper case.

    I have tried below code but i am missing some thing in it

    Sub UPcase()
    
    Dim NM As Name
    For Each NM In ActiveWorkbook.Names
    
    
    NM.Name = UCase(NM.Name)
    
    Next NM
    
    End Sub

    Thanks,

    Zaveri

    Monday, August 4, 2014 2:46 PM

Answers

  • Names are not case sensitive, so Excel does not care, and stays with the initial definition cases of the name. So you have to delete the name first and then re-create it:

    Sub UPcase2()
        Dim NM As Name
        Dim rng As Range
        Dim strN As String
        
        For Each NM In ActiveWorkbook.Names
            Set rng = NM.RefersToRange
            strN = NM.Name
            NM.Delete
            ActiveWorkbook.Names.Add UCase(strN), rng
        Next NM

    End Sub


    Monday, August 4, 2014 3:01 PM

All replies

  • Names are not case sensitive, so Excel does not care, and stays with the initial definition cases of the name. So you have to delete the name first and then re-create it:

    Sub UPcase2()
        Dim NM As Name
        Dim rng As Range
        Dim strN As String
        
        For Each NM In ActiveWorkbook.Names
            Set rng = NM.RefersToRange
            strN = NM.Name
            NM.Delete
            ActiveWorkbook.Names.Add UCase(strN), rng
        Next NM

    End Sub


    Monday, August 4, 2014 3:01 PM
  • Thanks.
    Tuesday, August 5, 2014 8:33 PM