Changing lowercase characters into uppercase is a pretty tedious task if you're doing it manually. In this article, we're going to show you how to make Excel change case to uppercase using VBA
Both Excel and VBA have a function to do this. Both use a single argument which is the string value to be converted to uppercase characters. Only difference is the name:
- Upper (Excel)
- UCase (VBA)
Since we are looking into doing this using VBA, we will be using the UCase function. To use UCase with all cells in a selection or a specified range, we need to check every cell using a loop. For Each…Next loop can move from cell to cell without being restricted by range dimensions. Apply the UCase function to each cell value and replace it.
You can use codes in two ways:
- Module
- Immediate Window
In the Module method, you need to add the module into the workbook or the add-in file. Copy and paste the code into the module to run it. The main advantage of the module method is that it allows saving the code in the file, so it can be used again later. Furthermore, the subroutines in modules can be used by icons in the menu ribbons or keyboard shortcuts. Remember to save your file in either XLSM or XLAM format to save your VBA code.
The Immediate Window method, on the other hand, is essentially a quick and dirty method where you can simply copy and paste the code into the Immediate Window and press the Enter key to run it. Unfortunately, any code you use in the Immediate Window will not be saved. Also note that icons and keyboard shortcuts will not be available.
Making Excel change case to upper case
Module Version:
Sub ConvertUppercaseInSelection() Dim rng As Range For Each rng In Selection rng.Value = UCase(rng.Value) Next rng End Sub
Immediate Version:
For Each rng In Selection: rng.Value = UCase(rng.Value): Next rng
Convert a specified range to uppercase
Module Version:
Sub ConvertUppercaseInRange() Dim rng As Range For Each rng In Range("B2:E11") rng.Value = UCase(rng.Value) Next rng End Sub
Immediate Version:
For Each rng In Range("B2:E11"): rng.Value = UCase(rng.Value): Next rng
Convert entire worksheet to uppercase
Module Version:
Sub ConvertUppercaseInWorksheet() Dim rng As Range For Each rng In ActiveSheet.UsedRange rng.Value = UCase(rng.Value) Next rng End Sub
Immediate Version:
For Each rng In ActiveSheet.UsedRange: rng.Value = UCase(rng.Value): Next rng