Wednesday, 8 February 2017

MS Excel - Change Text from Lowercase to Uppercase using Excel Macro

Recently we had to convert all text in an excel file containing a list of books to uppercase. Here is an Excel macro to get this work done quickly.

Copy the code given below

Sub AllTextToUpper()
    Dim myRng As Range
    Dim celRng As Range
    On Error Resume Next
    Set myRng= Cells.SpecialCells(xlCellTypeConstants, 2)
    For Each celRng In myRng
        celRng.Value = UCase(celRng.Value)
    Next celRng 
End Sub

Open the Excel file and press Alt+F11 (Hold Alt key and then press F11). This will bring up Miscrosoft VBA editor. Paste the above code as shown in the image below.

  • Close VBA Editor and come back to Excel (Or press Alt+F11 again)
  • Now Press Alt+F8 (or Click View->Macros->View Macros). The following dialog box will be visible.

  • Select AllTextToUpper and then Click Run
  • All the text in your excel sheet will be converted to Uppercase
Note: Please save your original Excel sheet before running macros. This action cannot be reversed.

No comments:

Post a Comment