Capitals

[rexmann]

Hi All

A bit of an odd one but is there a way to select all cells and make all text
capitals? I know there is a formula but I was hoping to do Ctrl A and then
turn all small case to capitals.

Any help greatly appreciated

Cheers Rexmann

Ps using excel 2003

 

[Jim Thomlinson]

You can do it but you need to use a macro something like this...

public sub MakeAllCaps
dim rng as range

on error resume next
for each rng in intersect(usedrange, selection)
rng.value = ucase(rng.value)
next rng
on error goto 0
end sub

 

[Jim Thomlinson]

Sorry I should have mentioned. That code will convert formulas to values...
If you have formulas that you do not want to overwrite then try this...

public sub MakeAllCaps
dim rng as range

on error resume next
for each rng in intersect(usedrange,
selection.SpecialCells(xlCellTypeConstants))
rng.value = ucase(rng.value)
next rng
on error goto 0
end sub

 

[rexmann]

Nice one thank you

Cheers Rexmann

You can do it but you need to use a macro something like this...

public sub MakeAllCaps
dim rng as range

on error resume next
for each rng in intersect(usedrange, selection)
rng.value = ucase(rng.value)
next rng
on error goto 0
end sub