Hello, Excellers. Welcome back to another #MacroMonday blog post in my #ExcelTips series for 2021. You may or may not already know how to convert all text to upper case in Excel by using the UPPER function. It works. But, it takes a bit longer than this little Excel Macro I am going to show you today because yes it is indeed VBA code time.
So, if you want more Excel and VBA tips then sign up for my monthly newsletter where I share 3 Excel Tips on the first Wednesday of the month and receive my free Ebook, 30 Excel Tips.
What Does The Macro Do?
This macro will convert all cells in the user selected range to UPPER CASE.
How Does The Macro Work?
Step 1. Open The Visual Basic Editor.
There are a couple of ways to do this. Hit ALT +F11 or Developer Tab – Visual Basic – Insert Module. Both have the same result. Now, you need to decide where you are going to insert that new module. There are two choices. In the current workbook you are working on, or your Personal Macro Workbook. So, what is the difference?.
To reuse the code over and over again, save the code in a module in your Personal Macro Workbook. If you want to read more about the Personal Macro Workbook then feel free to read my blog posts below.
Creating and Updating Your Personal Macro Workbook
Create A Shortcut To Your Personal Excel Macro Workbook
Why Is My Personal Macro Workbook Not Loading Automatically?
So, it makes sense in this instance to save the code in my Personal Macro Workbook so I can reuse this over and over again really conveniently.
Step 2. Name the Macro.
Next name your macro. I have named my macro UppperCaseRange. Type Sub and then the name of your macro. Excel will automatically also add in the End Sub line of code. All that is needed now is to write the code within these two lines.
Sub UpperCaseRange()
End Sub
Step 3. Declare Any Variables.
The next step in this macro is to declare any variables. I have only one variable to declare is thin macro. This will create a type of memory container for the range of cells selected by the user to convert to upper case.
Dim cell as Range
Step 4. Loop Through The Selected Range.
Excel begins to loop through the selected range of cells. This is done using the For Each Loop method. The syntax of this is
For Each object-name In Range
In this macro it is the cell in the selected range that the user has selected.
For Each cell In Selection.Cells
Step 5. Excluding Cells That Contain Formulas.
This line of code excludes any cells that contain formulas from being converted to upper case. Only if the if statement that the cell contains a formula is the cell converted to upper case.
If cell.Formula = False then
Step 6. Convert The Cells To Upper Case.
If the cell does not contain a formula it is converted to upp ercase and the loop begins again.
cell = UCase(cell)
End If
Next
Step 7. Ending The Macro.
Once all of the cells have been looped and converted to upper case then the macro ends. This last line of the code was already entered by Excel when the macro was named.
Want To Copy The Code? Easily Convert Your Cells To Upper Case.
'macro by howtoexcelatexcel.com
Sub UpperCaseRange()
Dim cell As Range
For Each cell In Selection.Cells
If cell.HasFormula = False Then
cell = UCase(cell)
End If
Next
End Sub
Finally, if you want to see all of my blog posts in the Macro Mondays Series you can find them all in the link below. Why not bookmark it? Yes, it is updated EVERY Monday.
How To Excel At Excel – Macro Mondays Blog Posts.
