How to uppercase text using an Excel macro instead of the UPPER function

Although you can use the =UPPER() function or formula to lowercase all the text in a cell, there are times when you may want to uppercase the text in the same cell the data was entered, programmatically. Just follow these steps:

1. Hit [ALT]+[F11] to open the Visual Basic Editor

2. Hit [ALT]+[I]+[M], or just click “Insert” > “Module”

3. In the pop-up window copy the ff. code and edit the range “A1:A5”:

Sub Uppercase()
   ' Loop to cycle through each cell in the specified range.
   For Each x In Range("A1:A5")
      ' Change the text in the range to uppercase letters.
      x.Value = UCase(x.value)
   Next
End Sub

4. Hit [ALT]+[Q], or just click “File” > “Close and return to Microsoft Excel”

5. Run the Macro in Excel by going to:

Microsoft 2007: View > Macro > Macro and then Run “Uppercase”

Microsoft 2010: Tools > Macros > View Macros and then Run “Uppercase”.

This will uppercase all the text in the range you specified on the selected worksheet (cells A1:A5 in the example code above).

 

Want to lowercase text using an Excel macro? View this tutorial »

Want to capitalize just the first character in every word using an Excel macro? View this tutorial »

This entry was posted in Macros. Bookmark the permalink. Post a comment or leave a trackback: Trackback URL.

2 Comments

  1. Faye Kercher
    Posted August 8, 2014 at 9:33 am | Permalink

    Do you have the instructions in this format for How to format all data to text?

    • karen
      Posted August 8, 2014 at 9:51 am | Permalink

      Hi Faye! I am not sure I understood your question. Could you explain what you mean by “format all data to text”? I’d love to see if I can help!

Post a Comment

Your email is never published nor shared. Required fields are marked *

You may use these HTML tags and attributes <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

*
*