Hello Excellers. Welcome back to another #MacroMonday blog post. Today let’s look at how to create a simple Excel VBA Macro which will be useful for converting your data to uppercase on your Excel worksheet. I use this every week, I have a data extract that always has a mix of upper and lower case. As well being annoying (for me – just a personal thing), in inconsistent data affects other parts of the worksheet solution in with data matching. So, I have this simple piece of VBA code which converts selected cells in my worksheet to upper case. Easy as that. I have stored this macro in my Personal Macro Workbook so I can use it in any open Excel worksheet.
Learn More About Your Personal Macro Workbook (PMW)
If you want to read more about your Excel PMW then check out my blog posts below.
Macro Mondays -Creating and Updating Your Personal Macro Workbook
Macro Mondays – Create A Shortcut To Your Personal Excel Macro Workbook
Why Is My Personal Macro Workbook Not Loading Automatically?
Don’t forget to follow me on YouTube for Excel Tutorials and Tips.
How Does The Macro Work?
The Macro uses the For Loop which is probably one of the most powerful programming techniques. The For Loop is typically used to move sequentially through a list of items or numbers. This is exactly what we need to loop through all of the selected cells and convert any text to uppercase.
Starting The Macro.
First of all, we start this code by opening the Excel Visual Basic Editor. You can either do this by hitting ALT+F11 or by selecting the Developer Tab | Visual Basic. This allows us to Insert a new Module to store our VBA code. We have two choices as to where to store the code.
- If we want to only use this code in this particular workbook then select to insert a Module within this workbook.
- While, in contrast, if you want to be able to reuse the code then insert the new Module into the PMW. The Excel VBA code stored in your PMW will be made available in any active Excel session that you open. As a result, it is really easy to keep all off your Excel Macro’s together in one place.
Naming The Macro
Type Sub then the name of your Macro, in this example, I am naming it ConvertToUpperCase
[stextbox id=’info’]
[/stextbox]
Notice Excel will insert the End Sub automatically for us to end the Sub Routine.
Declaring Variables
We need to declare a variable in the code. This action simply creates a memory container for these values. In this example today we are allocating memory for the range of cells selected by the user to convert to uppercase.
[stextbox id=’info’]
[/stextbox]
Begin Looping Through All Of Cells In The User Selected Range
The code begins to loop through the cells that have been selected by the user and converts them to UPPERCASE
[stextbox id=’info’]
Cell.Value = UCase(Cell)
Next Cell
[/stextbox]
Ending The Routine
Once all of the user selected cell have been converted to UPPERCASE then the routine ends
[stextbox id=’info’]
[/stextbox]
Putting All Of The VBA Code Together And Running The Macro
So, this is my favourite part of the whole process. Getting to test the code. Select the name from the drop-down list in the Macro Group in the Developer Tab. You can now use the Excel For Loop to convert all of those upper and lower case cells all to upper case with a few clicks.
Copy The VBA Code.
Here is all of the code in one place.
[stextbox id=’info’]
Sub ConvertToUpperCase()
Dim Rng As Range
For Each Cell In Selection.Cells
Cell.Value = UCase(Cell)
Next Cell
End Sub
[/stextbox]
What Next? Want More Tips?
So, if you want more tips then sign up for my Monthly Newsletter where I share 3 Tips on the first Wednesday of the month and receive my free Ebook, 30 Excel Tips.
If you want to see all of the blog posts in the Macro Monday series. Click on the link below
How To Excel At Excel – Macro Mondays Blog Posts.
Do You Need Help With An Excel Problem?.
Finally, I am pleased to announce I have teamed up with Excel Rescue, where you can get help FAST. All you need to do is choose the Excel task that most closely describes what you need to be done. Above all, there is a money back guarantee and similarly Security and Non-Disclosure Agreements. Try It!. Need Help With Excel VBA code at all?. Of course, you do not need to ask how to Convert All Cells To Uppercase as you have that solution right here for free!.
