Converting Worksheet to all 'Upper'

[ADArnold]

Hi All,

I know how to change lower case columns to upper case with th
=upper(cell) function. Then once the new 'upper' column is complete
copy, then paste back as 'values' and then delete the old 'lower
column.

Gosh, I have over a dozen columns to convert in each worksheet. Isn'
there a way to convert the whole worksheet to upper in one fell swoop?

While I'm here, I'd like to thank all those regulars here for the hel
they have contributed on a daily basis to all of us not s
knowledgable.

You have saved many thousands of hours of frustration, and I, for one
am grateful!

A Million THANKS FOR A JOB WELL DONE

 

[Paul B]

AD, here is one way

Sub Upper_Case()
Application.ScreenUpdating = False
For Each c In ActiveSheet.UsedRange
c.Value = UCase(c)
Next
Application.ScreenUpdating = True
End Sub


--
Paul B
Always backup your data before trying something new
Please post any response to the newsgroups so others can benefit from it
Feedback on answers is always appreciated!
Using Excel 2000 & 97
** remove news from my email address to reply by email **

 

[ADArnold]

Paul B

Thanks for your quick response that worked great. One problem aros
that I should have expected:cells that had formulas in them ended up a
'Values'.

If I had done a search before posting (instead of after posting)
would have found this other elegant solution that works and doesn'
change formulas it runs across to Values:

Sub ConvertToUpperCase()
Dim Rng As Range
For Each Rng In Selection.Cells
If Rng.HasFormula = False Then
Rng.Value = UCase(Rng.Value)
End If
Next Rng
End Sub

I found this solution at http://www.cpearson.com/excel/case.htm

In any case, thanks for taking the time to provide a macro that solve
the problem and I apologize for not doing a search before I posted

 

[David McRitchie]

Hi Arnold,

If I were to run the example you found, I would have to allow
about 3 minutes per column on my 600mHz machine. Even on
a faster machine you would certainly notice the time consumed.

If conversion to uppercase were an Excel builtin conversion
you would certainly be selecting entire columns and not taking
extra pains to select a range that falls within your used cell
range -- the software or macro should be doing that for you.

It would be better to let the macro restrict the actual scope
within your selection so that you can select entire columns
and quickly perform your conversion, just like you would if this
were builtin to Excel.

My entire page concerns writing macros to run faster and to
help you recognize solutions designed to run faster. Take a look at
http://www.mvps.org/dmcritchie/excel/proper.htm#upper
which is part of web page
Proper, and other Text changes -- Use of SpecialCells
http://www.mvps.org/dmcritchie/excel/proper.htm
which goes beyond even just simply turning off screen updating
and calculation when a macro is running.