Convert all text in a sheet to UpperCase?

[Rich]

Hello,

Is there a command to convert all text in a sheet to UpperCase in one shot?
Or do I have to loop?

Dim rng As Range, i As Integer, j As Integer
set rng = Sheet1.UsedRange
For i = 1 to rng.Rows.Count
For j = 1 to rng.Columns.count
rng(i,j) = ConvertToUpperCase(rng(i,j) '-- a udf, pseudo code whatever
Next
Next

Thanks,
Rich

 

[JE McGimpsey]

See

http://www.mvps.org/dmcritchie/excel/proper.htm#upper

 

[Rich]

Thank you for your reply. It looks like there is no way to get around
looping through the range of text to convert it to Ucase. I ended up doing
the looping on the range thing, it was pretty slow. I was hoping I could do
something like

Sheet1.UsedRange.Text = Ucase(Sheet1.UsedRange.Text)

But that did not work out. The idea was like you can select a range of
cells and hit ctrl-B to bold everything in one shot. That it was I was
hoping to achieve with Ucase. Like create a macro that I could call with
ctrl something but without looping.

 

[Gary Keramidas]

did you turn off screenupdating and calculation?

 

[Rich]

I found Calculations tab in Options menu item. I guess you turn it off by
checking "Manual". But how do you disable ScreenUpdating?

 

[JE McGimpsey]

One way:

At the beginning of your code:

With Application
.ScreenUpdating = False
.Calculation = xlCalculationManual
End With

At the end of your code:

With Application
.Calculation =xlCalculationAutomatic
.ScreenUpdating = True
End With

 

[ct60]

Hi Rich -

This is kind of a kooky way to do this, but i think it will work and should
be really fast. Try something like this:

sub convertRange2Upper(someRange as range)
dim replaceWhat as variant
dim replaceWith as variant
dim i as integer

replaceWhat = array("a","b","c",...)
replaceWith = array("A","B","C",...)

for i = 0 to 25
someRange.replace What:=replaceWhat(i), Replacement:=replaceWith(i), _

LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, _
SearchFormat:=False, _
ReplaceFormat:=False
next
End sub

This idea came to me as I read your post, but it should work because the
search replace is very fast. I am actually not sure what the replaceFormat
does - that might be worth checking into also.

Hope that helps.

Chris (ct60)

 

[Dave Peterson]

Or something like:

Option Explicit
Sub testme()
Call convertRange2Upper(ActiveSheet.Range("a:a"))
End Sub
Sub convertRange2Upper(someRange As Range)
Dim i As Long
For i = Asc("a") To Asc("z")
someRange.Replace What:=Chr(i), _
Replacement:=Chr(i - 32), _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
MatchCase:=False, _
SearchFormat:=False, _
ReplaceFormat:=False
Next i
End Sub

Maybe turning calculation to manual may help speed it up, too.

SearchFormat and ReplaceFormat were added in xl2002. You can actually specify
if you want the format (bold/font color/fill color) changed, too.

Try it under the Edit|Replace|Options button.

 

[ct60]

That's a good one, Dave

Chris

Or something like:

Option Explicit
Sub testme()
Call convertRange2Upper(ActiveSheet.Range("a:a"))
End Sub
Sub convertRange2Upper(someRange As Range)
Dim i As Long
For i = Asc("a") To Asc("z")
someRange.Replace What:=Chr(i), _
Replacement:=Chr(i - 32), _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
MatchCase:=False, _
SearchFormat:=False, _
ReplaceFormat:=False
Next i
End Sub

Maybe turning calculation to manual may help speed it up, too.

SearchFormat and ReplaceFormat were added in xl2002. You can actually specify
if you want the format (bold/font color/fill color) changed, too.

Try it under the Edit|Replace|Options button.