VBA Macro To Convert Text To Proper Case

[Howardc]

I have data in Col E. I would like a macro to convert the text from row 2 onwards into proper case

[Excel Fox]

Sub ConvertToProper()

    Dim lng As Long
    lng = Cells(Rows.Count, "E").End(xlUp).Row
    Range("E2:E" & lng).Value = Evaluate("=IF(E2:E" & lng & "<>"""",PROPER(E2:E" & lng & "),"""")")
    
End Sub

[Howardc]

Thanks for the help, much appreciated

[Rick Rothstein]

Sub ConvertToProper()
    Dim lng As Long
    lng = Cells(Rows.Count, "E").End(xlUp).Row
    Range("E2:E" & lng).Value = Evaluate("=IF(E2:E" & lng & "<>"""",PROPER(E2:E" & lng & "),"""")")
End Sub

You can shorten that Evaluate statement slightly by doing it this way...

Sub ConvertToProper()
  Dim lng As Long
  lng = Cells(Rows.Count, "E").End(xlUp).Row
  Range("E2:E" & lng).Value = Evaluate("IF(ROW(),PROPER(E2:E" & lng & "))")
End Sub

[Howardc]

Hi Rick, thanks for your input-much appreciated