How to capitalize all letters in all cells ?

RomulusMilea

Board Regular
Joined
May 11, 2002
Messages
171
Hello all,

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

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
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.
 

Barry Katcher

Well-known Member
Joined
Feb 25, 2002
Messages
4,053
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.
 

Forum statistics

Threads
1,144,274
Messages
5,723,440
Members
422,497
Latest member
dougy99

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top