Change Case with VBA

In this article I will show you how to change the case of a range automatically.  Traditionally in Excel you may wish to change the case of cells between Upper case, lower case and proper case.

The following is the procedure to change column A to upper case. I put a error trap in the code in case there are formulas.


Sub UpperCase()  'Excel VBA for upper case
Dim rng As Range

For Each rng In Range("A2", [a65536].End(xlUp)) 'Start the VBA loop
On Error Resume Next  'Error trap
rng=StrConv(rng.Text, vbUpperCase)
Next
On Error GoTo 0
End Sub

While the following Excel VBA procedure will change the cells to lower case.


Sub LowerCase()  'Excel VBA for lower case
Dim rng As Range

For Each rng In Range("A2", [a65536].End(xlUp)) 'Start the VBA loop
On Error Resume Next  'Error trap
rng=StrConv(rng.Text, vbLowerCase)
Next
On Error GoTo 0
End Sub

Finally this will change the cells in column A to proper case. This is capitalising the first letter of each word.


Sub ProperCase()  'Excel VBA for proper case
Dim rng As Range

For Each rng In Range("A2", [a65536].End(xlUp)) 'Start the VBA loop
On Error Resume Next  'Error trap
rng=StrConv(rng.Text, vbProperCase)
Next
On Error GoTo 0
End Sub

The following file outlines the three Excel VBA procedures.