Thread: Capitalizing an entire work sheet

Perhaps:

Option Explicit
Sub MakeProper()
Dim rngCell As Range
Dim lngCalc As Long
With Application
    lngCalc = .Calculation
    .Calculation = xlManual
    .ScreenUpdating = False
End With
For Each rngCell In Cells.SpecialCells(xlCellTypeConstants)
    rngCell = Evaluate("PROPER(" & rngCell.Address & ")")
Next rngCell
Set rngCell = Nothing
With Application
    .Calculation = lngCalc
    .ScreenUpdating = True
End With
End Sub

Possibly using Evaluate method ?
Very basic example I'm afraid as I'm heading off for lunch...

Public Sub DoItProper()
Dim rngData As Range, strRng As String
With ActiveSheet
    Set rngData = .UsedRange
    strRng = rngData.Address
    rngData.Value = .Evaluate("=IF(ROW(" & strRng & "),PROPER(" & strRng & "))")
    Set rngData = Nothing
End With
End Sub

EDIT - in essence the above is a reiteration of JvH's post...

Try this code. It will apply the PROPER worksheet function to the range you have selected.

Sub a()
Dim rCell As Range
For Each rCell In Selection
rCell.Value = Application.WorksheetFunction.Proper(rCell.Value)
Next
End Sub

Originally Posted by DonkeyOte

Possibly using Evaluate method ?
Very basic example I'm afraid as I'm heading off for lunch...

Public Sub DoItProper()
Dim rngData As Range, strRng As String
With ActiveSheet
    Set rngData = .UsedRange
    strRng = rngData.Address
    rngData.Value = .Evaluate("=IF(ROW(" & strRng & "),PROPER(" & strRng & "))")
    Set rngData = Nothing
End With
End Sub

EDIT - in essence the above is a reiteration of JvH's post...

But without the loop. Very clever Luke!

I can't take credit for that - I picked it up from Pedro (c/o Bill Jelen's book)... moreover my quick demo makes assumptions of there being no formulae etc... to limit processing as much as possible (and so as to avoid overriding formulae) perhaps:

Public Sub DoItProper()
Dim rngData As Range, rngArea As Range, strRng As String
With ActiveSheet
    Set rngData = .UsedRange.SpecialCells(xlCellTypeConstants,xlTextValues)
    For Each rngArea in rngData.Areas
        strRng = rngArea.Address
        rngArea.Value = .Evaluate("=IF(ROW(" & strRng & "),PROPER(" & strRng & "))")
        Set rngArea = Nothing
    Next rngArea
    Set rngData = Nothing
End With
End Sub

The use of SpecialCells(xlCellTypeConstants, xlTextValues) is imperative because:

1. it reduces the size of the range to loop through (which can be avoided using Lukes method)

and

2. because without it, it will convert formula to constants.