How to convert selection to Excel lowercase using VBA

by | Oct 18, 2018 | Excel Macros, Excel Tips & Tricks

How to convert selection to Excel lowercase using VBA

Is changing the all letters to lowercase manually taking too much time? Let us show you how to convert selection to Excel lowercase using VBA and automate this process.

Both Excel and VBA have a function to do this. Both functions use a single argument which is the string value to made lowercase. Only difference is the name:

  • Lower (Excel)
  • LCase (VBA)

In the VBA method, we will be using the LCase function. To use LCase with all cells in a selection or a specified range, we need to check every cell with a loop like For Each…Next which can move from cell to cell regardless of range dimensions. The idea is to apply the LCase function on 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 that 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.

 

Convert selection to lowercase

Module Version:

Sub ConvertLowercaseInSelection()

    Dim rng As Range

    For Each rng In Selection

        rng.Value = LCase(rng.Value)

    Next rng

End Sub

Immediate Version:

For Each rng In Selection: rng.Value = LCase(rng.Value): Next rng

 

Convert a specified range to lowercase

Module Version:

Sub ConvertLowercaseInRange()

Dim rng As Range

For Each rng In Range("B2:E11")

rng.Value = LCase(rng.Value)

Next rng

End Sub

Immediate Version:

For Each rng In Range("B2:E11"): rng.Value = LCase(rng.Value): Next rng

 

Convert entire worksheet to lowercase

Module Version:

Sub ConvertLowercaseInWorksheet()

Dim rng As Range

For Each rng In ActiveSheet.UsedRange

rng.Value = LCase(rng.Value)

Next rng

End Sub

Immediate Version:

For Each rng In ActiveSheet.UsedRange: rng.Value = LCase(rng.Value): Next rng