How do I convert spreadsheet into proper format?

G

gurunner

I am trying to convert my text into proper format all at one time so I don't
have to do each cell individually. Can anyone help me?
 
D

Don Guillett

one way to try
Sub makeproper()
With ActiveSheet.UsedRange
..Value = Application.Proper(.Value)
End With
End Sub
 
D

Don Guillett

if you have formulas, use this instead or you will wipe out the formulas
Sub makeproper()
With ActiveSheet.UsedRange.SpecialCells(xlCellTypeConstants)
..Value = Application.Proper(.Value)
End With
End Sub
 
D

Dave Peterson

If the range that has the constants is multi-area, then this gives xl2002
problems (xl2k and up, IIRC, ok in xl97).

I think I'd go through each area:

Option Explicit
Sub testme01()

Dim myArea As Range
Dim myRng As Range

With ActiveSheet
Set myRng = Nothing
On Error Resume Next
Set myRng = .UsedRange.SpecialCells(xlCellTypeConstants)
On Error GoTo 0
If myRng Is Nothing Then
MsgBox "no constants"
Else
For Each myArea In myRng.Areas
With myArea
.Value = Application.Proper(.Value)
End With
Next myArea
End If
End With
End Sub
 
Top