UPPER CASE

J

JASON

How do I conver one column to upper case in a macro.
any help would be appreciated
thanks
 
P

Paul B

Jason, here is one way, select your range and run this

Sub CAPS()
'select range and run this to change to all CAPS
Dim cel As Range
For Each cel In Intersect(Selection, _
ActiveSheet.UsedRange)
cel.Formula = UCase$(cel.Formula)
Next
End Sub


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

keepitcool

Jason,
following should work in most situations.

if 1 cell is selected it picks the current region,
else it processes the text values in the selection.

Sub MakeUpper()
Dim c As Range
on error resume next 'to avoid prob if no text cells are found
If TypeName(Selection) <> "Range" Then Beep: Exit Sub
If Selection.Count = 1 Then Selection.CurrentRegion.Select
With Selection.Cells.SpecialCells(xlCellTypeConstants, xlTextValues)
For Each c In .Cells
c = UCase(c)
Next
End With
End Sub


keepITcool

< email : keepitcool chello nl (with @ and .) >
< homepage: http://members.chello.nl/keepitcool >
 
D

David McRitchie

You've made a single cell selection expand to the current region.
That is the default and exactly what I would try to avoid.

The important thing is that you have limited the cells to the used range
and prevented formulas from being converted to values. Paul's
solution prevents formulas from being changed to values by assigning
a formula to a formula, either way prevents loss of formulas..

The following includes a few more things to make the macro run
faster, like turning off screen updating and calculation.

http://www.mvps.org/dmcritchie/excel/proper.htm#upper
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"keepitcool" <[email protected]
 
Top