proper case a a whole range

[Trevor3007]

hi.

data in the range i3:i8000

is there away to force the data alredy in the above range to lowercase ?(i have put some code in so future any data entered will do this)... is there a way to do this without having to change each line manually?

MTIA

 

[KOKOSEK]

Sub MakeItSmall()
Dim cell As Range
For Each cell In [I3:I8000]
    cell.Value = LCase(cell.Value)
Next cell
End Sub

 

[Fluff]

Another option

Sub Trevor3007()
   With Range("I3", Range("I" & Rows.Count).End(xlUp))
      .Value = Evaluate(Replace("if(@="""","""",proper(@))", "@", .Address))
   End With
End Sub

 

[Rick Rothstein]

Another option

Sub Trevor3007()
   With Range("I3", Range("I" & Rows.Count).End(xlUp))
      .Value = Evaluate(Replace("if(@="""","""",[B][COLOR="#FF0000"]LOWER[/COLOR][/B](@))", "@", .Address))
   End With
End Sub

I know the thread title say "Proper Case", but the OP's description says he wants "Lower Case".

 

[Trevor3007]

Another option

Sub Trevor3007()
   With Range("I3", Range("I" & Rows.Count).End(xlUp))
      .Value = Evaluate(Replace("if(@="""","""",proper(@))", "@", .Address))
   End With
End Sub

Hi Fluff,

thanks for the VB...works a treat many thanks.

 

[Trevor3007]

Sub MakeItSmall()
Dim cell As Range
For Each cell In [I3:I8000]
    cell.Value = LCase(cell.Value)
Next cell
End Sub

hello KOKOSEK ,

thank you for your reply. I am unsure why you sent VB for 'lower' as i requested VB for Proper Case, but many thanks anyhoo...i am sure i willl need & use soon.

 

[Trevor3007]

I know the thread title say "Proper Case", but the OP's description says he wants "Lower Case".

good evening double R,

thanks for your help & my OP should of been 'Proper Case' not lower. sorry my bad.

 

[Trevor3007]

Sub MakeItSmall()
Dim cell As Range
For Each cell In [I3:I8000]
    cell.Value = LCase(cell.Value)
Next cell
End Sub

hello,

DOH... now I know why the 'lower' VB. my OP stated this...my bad

 

[alansidman]

An another means:

Load your data into Power Query. Select All Columns. Select Transform. Select Format. Select Lowercase. Close and Load.

 

[Fluff]

You're welcome & thanks for the feedback