Convert Case

[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?

 

[PY & Associates]

D6=upper(A6)
highlight D6
drag to E6
highlight D6:E6
drag to E11999

 

[Jacob Skaria]

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

 

[keiji kounoike]

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

 

[Rick Rothstein]

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