Thursday, 9 June 2011

Excel Macro to convert the CASE of a range of TEXT

Really useful macro. I used it to clean up surveyshare data file before bringing it to SPSS.

Before using:

"Uncomment" (remove the apostrophe from) the line of code that changes the text to the case you want. For example I needed everything to converted to lower case and hence I removed the apostrophe from "' Rng.Value = StrConv(Rng.Text, vbLowerCase)"

Sub ChangeCase()
Dim Rng As Range
On Error Resume Next
Application.EnableEvents = False
For Each Rng In Selection.SpecialCells(xlCellTypeConstants, _
If Err.Number = 0 Then
' Rng.Value = StrConv(Rng.Text, vbUpperCase)
' Rng.Value = StrConv(Rng.Text, vbLowerCase)
' Rng.Value = StrConv(Rng.Text, vbProperCase)
End If
Next Rng
Application.EnableEvents = True
End Sub


No comments: