Convert all text in a sheet to UpperCase?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
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.
 
I found Calculations tab in Options menu item. I guess you turn it off by
checking "Manual". But how do you disable ScreenUpdating?
 
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
 
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)
 
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.
 
That's a good one, Dave

Chris

Dave Peterson said:
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.
Click to expand...
 
Back
Top