Convert Case

  • Thread starter Thread starter ordnance1
  • Start date Start date
O

ordnance1

My original post seems to have disappeared so here is a repeat.

I have to clean up a spreadsheet by converting the text in cells A6:B11999
to uppercase text. These cells contain text only. Is there a quick way to
accomplish this?
 
Since you have posted this query in Excel Programming group; I assume you
expect a VBA solution. You can try out the below macro. If you are new to
macros..

--Set the Security level to low/medium in (Tools|Macro|Security).
--From workbook launch VBE using short-key Alt+F11.
--From menu 'Insert' a module and paste the below code.
--Get back to Workbook.
--Run macro from Tools|Macro|Run <selected macro()>


Sub Macro()
Dim cell As Range
For Each cell In Range("A6:B11999")
If cell.Text <> "" Then cell.Value = UCase(cell.Text)
Next
End Sub
 
I would write some short macro. For example,

Sub Lower2Upper()
Dim rng As Range
Set rng = Range("A6:B11999")
rng.Value = Evaluate("=if(upper(" & rng.Address & ")<>"""", upper(" & _
rng.Address & "),"""")")
End Sub

This would take some time, but only you need is just to wait to be done.

Keiji
 
This macro should be nicely efficient...

Sub MakeUpperCase()
Dim Cell As Range, CellRange As Range, FirstAddress As String
Set CellRange = Range("A6:B11999")
Set Cell = CellRange.Find("*", SearchDirection:=xlNext)
If Not Cell Is Nothing Then
FirstAddress = Cell.Address
Do
Cell.Value = UCase(Cell.Value)
Set Cell = CellRange.FindNext(Cell)
Loop While Cell.Address <> FirstAddress And Not Cell Is Nothing
End If
End Sub
 
Back
Top