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?

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


On Mar 3, 3:22*pm, "ordnance1" <ordnan...@comcast.net> wrote:
> 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

--
Jacob


"ordnance1" wrote:

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

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

ordnance1 wrote:
> 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?

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

--
Rick (MVP - Excel)


"ordnance1" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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?