Hello, Excellers. Time for some more #Macro fun. So, you may (or may not) know that you can change the case of text to a lower case using the Excel LOWER function Excel. Alternatively, a small bit of Excel VBA code will do the same over and over again automatically. Let’s get writing some simple VBA.
If you want more Excel and VBA tips then sign up to 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 any range of cells that the user selects into LOWERCASE if the cells contain a string.
Prepare To Write The Macro.
First, open the Visual Basic Editor. There are two ways to do this.
- Hit ALT +F11 or
- Select the Developer Tab | Code Group | Visual Basic. Both methods have the same result.
So, if you cannot see the Developer Tab you may need to enable it. Check out my very short YouTube video for instructions. Learn how to enable the developer tab in just a few minutes.
Where To Store The Excel Macro?.
Next, where to save your Excel macro?. So, you can either save the code in your current workbook or in your Personal Macro Workbook. If you want to reuse the macro over and over again (like this one) in many workbooks save it in the Personal Macro Workbook. If the code is only to be used in the current workbook, save it there. In this case, it makes sense to be able to access this macro for any workbook. Therefore, it is to be stored therefore in my Personal Macro Workbook.
Step 1. Insert A New Module.
The first step is to insert a new module into the workbook (either the current workbook or as stated already this one goes into my Personal Macro Workbook).
Hit the Insert Menu | Module. So, once the new module is inserted, type Sub then the name of the Excel macro. In this example, I have named my Macro LowerCaseRange. Excel automatically inserts the End Sub ending code once a new macro is created. Any other lines of code are placed in between these two lines of code.
Step 2. Declare Any Variables.
One variable needs to be declared. This allocates a memory container for the range of cells selected by the user to convert to lowercase.
Step 3. Set The Range To Convert To Upper Case.
Next this the code below sets the range that the use has selected as the range of cells to convert to lowercase.
Step 4. Check For Cells That Contain Formula. Convert Cells If They Do Not.
Now, the For Each looping method is used to check if any of the cells in the selection contain a formula. This is ideal. It executes a statement or a group of statements for each element in an array or collection. If any cells contain a formula then they are not converted. If they are not or ‘False’ then Excel goes ahead and converts the cells to lower case. This continues until all of the cells in the selections have been looped.
Step 5. Ending The Macro.
So, once all of the cells in the selected range have been converted to lower case then the code ends. This bit of code was automatically generated as the macro was named in the first stage of writing the code. Nice.
Then my favorite step test your code!
Want To Copy The Code?
In addition, 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.
Finally, you can also download the corresponding example workbook that contains the Macro code by clicking on the download button below.
Note. Always make a backup copy of your work before running any macros.