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

See

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

In article ,
Rich wrote:

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.



"JE McGimpsey" wrote:

See

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

In article ,
Rich wrote:

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

did you turn off screenupdating and calculation?

--


Gary


"Rich" wrote in message
...
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.



"JE McGimpsey" wrote:

See

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

In article ,
Rich wrote:

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

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


"Gary Keramidas" wrote:

did you turn off screenupdating and calculation?

--


Gary


"Rich" wrote in message
...
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.



"JE McGimpsey" wrote:

See

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

In article ,
Rich wrote:

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

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

In article ,
Rich wrote:

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


"Gary Keramidas" wrote:

did you turn off screenupdating and calculation?

--


Gary


"Rich" wrote in message
...
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.



"JE McGimpsey" wrote:

See

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

In article ,
Rich wrote:

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

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)

"Rich" wrote:

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

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 wrote:

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)

"Rich" wrote:

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

--

Dave Peterson

That's a good one, Dave

Chris

"Dave Peterson" wrote:

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 wrote:

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)

"Rich" wrote:

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

--

Dave Peterson