How to capitalize all letters in all cells ?

Thanks Thanks:  0
Likes Likes:  0
Results 1 to 4 of 4

Thread: How to capitalize all letters in all cells ?

  1. #1
    Board Regular
    Join Date
    May 2002
    Location
    Romania
    Posts
    168
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

     
    Hello all,

    Please help me to write an Excel VBA code which can rapidly capitalize all letters in all cells. Thank you in advance.

  2. #2
    Board Regular
    Join Date
    Jul 2002
    Location
    Derbyshire, UK
    Posts
    1,605
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi

    If you download the free asap addin from http://www.asap-utilities.com you can do this from the Text menu.

  3. #3
    MrExcel MVP
    Moderator
    Andrew Poulsom's Avatar
    Join Date
    Jul 2002
    Posts
    73,092
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Here you are:

    Code:
    Sub Test()
        Dim Rng As Range
        Dim c As Range
        Set Rng = Selection.SpecialCells(xlCellTypeConstants, 2)
        For Each c In Rng
            c.Value = UCase(c.Value)
        Next c
    End Sub
    You can either select the range you want to convert or just select one cell and it will convert all the cells on the worksheet.

  4. #4
    Board Regular Barry Katcher's Avatar
    Join Date
    Feb 2002
    Location
    Dog Beach, Florida. Yeaahh!
    Posts
    4,053
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

      
    An alternate solution, using formulas instead of VBA, is to set up a second sheet to mirror your first sheet and use the =UPPER() formula. Say, for instance, the range on sheet 1 you want to upper-case is A1 thru F15. Go to sheet 2, and, in cell A1, enter the formula =UPPER(Sheet1|A1). Copy this formula to A2 thru A15 and then from A1:A15 to B1:F15.

    Advantage to this is that you can enter new info or edit info on sheet 1 (within that range) and you don't have to run a macro to update to upper-case, it is automatically reflected on sheet 2.
    Barry-

    Photo Restoration/Enhancement

    http://www.smiledogproductions.com
    click below for detour


User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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

 

 
DMCA.com