Results 1 to 4 of 4

How to capitalize all letters in all cells ?

This is a discussion on How to capitalize all letters in all cells ? within the Excel Questions forums, part of the Question Forums category; Hello all, Please help me to write an Excel VBA code which can rapidly capitalize all letters in all cells. ...

  1. #1
    Board Regular
    Join Date
    May 2002
    Location
    Romania
    Posts
    166

    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,167

    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
    64,505

    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,055

    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


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