vba capitalize 1st letter of each word then exit sub after correction

[Pinaceous]

Hi All,

I'm working with a sub trying to capitalize the first letter of each word in a column:

Sub CapitalizeFirstWord1()
Dim rng As Range
For Each rng In Range("BA11:BA180")
rng.Value = StrConv(rng.Value, vbProperCase)
Next rng
Exit Sub
End Sub

However, when I apply this sub to its table column it takes to long to execute.

Do you know how I can add to this code, when it finds the lower-case word(s) in cell to then exit the sub in lieu of checking every cell in that column?

Please let me know.

Thank you very much!
pinaceous

 

[Alex Blakenburg]

Give this a try.

Sub CapitalizeFirstWord1_Mod()
    Dim rng As Range
    Dim arr As Variant
    
    Set rng = Range("BA11:BA180")
    arr = rng.Value
    arr = Application.Proper(arr)
    rng.Value = arr
End Sub

 

[Pinaceous]

Give this a try.

Sub CapitalizeFirstWord1_Mod()
    Dim rng As Range
    Dim arr As Variant
   
    Set rng = Range("BA11:BA180")
    arr = rng.Value
    arr = Application.Proper(arr)
    rng.Value = arr
End Sub

Thank you Alex Blakenburg!

 

[Alex Blakenburg]

You're welcome. Glad I could help.