How to capitalize all letters in all cells ?

[RomulusMilea]

Hello all,

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

 

[royUK]

Hi

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

 

[Andrew Poulsom]

Here you are:

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.

 

[Barry Katcher]

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.