proper case a a whole range

Trevor3007

Well-known Member
Joined
Jan 26, 2017
Messages
514
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
 

Some videos you may like

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.

KOKOSEK

Board Regular
Joined
Apr 8, 2019
Messages
226
Office Version
365, 2013
Platform
Windows
Code:
Sub MakeItSmall()
Dim cell As Range
For Each cell In [I3:I8000]
    cell.Value = LCase(cell.Value)
Next cell
End Sub
 
Last edited:

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
42,864
Office Version
365
Platform
Windows
Another option
Code:
Sub Trevor3007()
   With Range("I3", Range("I" & Rows.Count).End(xlUp))
      .Value = Evaluate(Replace("if(@="""","""",proper(@))", "@", .Address))
   End With
End Sub
 

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,048
Office Version
2010
Platform
Windows
Another option
Code:
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".
 
Last edited:

Trevor3007

Well-known Member
Joined
Jan 26, 2017
Messages
514
Another option
Code:
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

Well-known Member
Joined
Jan 26, 2017
Messages
514
Code:
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

Well-known Member
Joined
Jan 26, 2017
Messages
514
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

Well-known Member
Joined
Jan 26, 2017
Messages
514
Code:
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

Well-known Member
Joined
Feb 26, 2007
Messages
5,691
Office Version
2019
Platform
Windows
An another means:

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

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
42,864
Office Version
365
Platform
Windows
You're welcome & thanks for the feedback
 

Watch MrExcel Video

Forum statistics

Threads
1,102,713
Messages
5,488,460
Members
407,638
Latest member
brandynl

This Week's Hot Topics

  • Timer in VBA - Stop, Start, Pause and Reset
    [CODE=vba][/CODE] Option Explicit Dim CmdStop As Boolean Dim Paused As Boolean Dim Start Dim TimerValue As Date Dim pausedTime As Date Sub...
  • how to updates multiple rows in muliselect listbox
    Hello everyone. I need help with below code. code is only chaning 1st row in mulitiselect list box. i know issue with code...
  • Delete Row from Table
    I am trying to delete a row from a table using VBA using a named range to find what I need to delete. My Range is finding the right cell. In the...
  • Assigning to a variable
    I have a for each block where I want to assign the value in column 5 of the found row to the variable Serv. [CODE=vba] For Each ws In...
  • Way to verify information
    Hi All, I don't know what to call this formula, and therefore can't search. I have a spreadsheet with information I want to reference...
  • Active Cell Address – Inactive Sheet
    How to use VBA to get the cell address of the active cell in an inactive worksheet and then place that cell address in a location on the current...
Top