How to capitalize all letters in all cells ?

RomulusMilea

Board Regular
Joined
May 11, 2002
Messages
176
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

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.
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.
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,559
Messages
6,114,302
Members
448,564
Latest member
ED38

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
Back
Top