Excel Caps

[BullseyeThor]

Hi All

I have an Excel sheet with a lot of information on it, however the information is in capitol letters and I don't want it to be. Is there a way that I can make the document auto change all of the caps to lower except the first letter of each word??

Thanks

 

[Gerald Higgins]

One way, maybe not the best, depending on how much data you have, would be to use the =PROPER() function.

 

[RoryA]

Try running this:

Sub ProperCase()
    Dim rngCell As Range
    Application.ScreenUpdating = False
    For Each rngCell In ActiveSheet.SpecialCells(xlCellTypeConstants)
      rngCell.Value2 = StrConv(rngCell.Value2, vbProperCase)
   Next rngCell
   Application.ScreenUpdating = False
   
End Sub

 

[MickG]

Try:-

With ActiveSheet
    .UsedRange = Application.Proper(.UsedRange)
End With

Mick

 

[BullseyeThor]

The information I have is huge from A1 to C54379

I have inserted the code but I get runtime error 91 or it just doesn't work. Should I be changing anything in the code to make it work?

 

[RoryA]

You may have too much for specialcells, so let's try brute force:

Sub ProperCase()
    Dim rngcell As Range
    Application.ScreenUpdating = False
    For Each rngcell In ActiveSheet.UsedRange
      If Not rngcell.HasFormula And Len(rngcell.Value2) > 0 Then
         rngcell.Value2 = StrConv(rngcell.Value2, vbProperCase)
      End If
   Next rngcell
   Application.ScreenUpdating = False
   
End Sub

 

[MickG]

Try this, it worked for me:-

[COLOR="Navy"]Sub[/COLOR] MG18May33
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Dim[/COLOR] Ray
[COLOR="Navy"]Set[/COLOR] Rng = Range("A1:C65000")
 Ray = Rng.value
  Ray = Application.Proper(Ray)
   Rng = Ray
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick