Change Text Case

[Guest]

I want to be able to run a Macro that chnages all the cell contents from
UPPERCASE to Titlecase. I know hwo to do it in Word but can this be done in
Excel?

Tia


Jonathan

 

[Norman Jones]

Hi Johnathan,

Try:

'===============>>
Sub MakeProperCase()
Dim rCell As Range
On Error Resume Next
For Each rCell In selection.Cells
If Not rCell.HasFormula Then
rCell.value = Application.Proper(rCell.value)
Else
ActiveCell.Formula = Application.Proper(ActiveCell.Formula)
End If
Next myCell
End Sub
'<<===============

If this is a frequent requirement, you might wish to add a toolbar button
and assign the macro to the new button.

 

[Guest]

Hi Norman,

Thanks for the code it works but it is taking for ever to run becuase I have
a large amount of data, rather than using a loop is there a way I can acheive
the same results but using a selection of columns i.e. A:G only?

 

[Norman Jones]

Hi Jonathan,

Try:
'====================>>
Public Sub MakeProperCase()
Sub MakeProperCase()
Dim ws As Worksheet
Dim rng As Range
Dim rCell As Range

Set ws = ActiveSheet

On Error Resume Next
Set rng = Columns("A:G").SpecialCells(xlCellTypeConstants, 2)
On Error GoTo 0

If Not rng Is Nothing Then

Application.ScreenUpdating = False

For Each rCell In rng.Cells
rCell.Value = Application.Proper(rCell.Value)
Next rCell

Application.ScreenUpdating = True

End If

End Sub
'<<====================