specify cell selection in a marco

[chubach]

I found a useful macro online but it only selects cells A1:A5 and I need to
modify it so I can select a range of cells by highlighting them, and then run
the macro. I tried to delete part of the syntax but just get error messages.
The macro I have now is:

Sub Uppercase()
' Loop to cycle through each cell in the specified range.
For Each x In Range("A1:A5")
' Change the text in the range to uppercase letters.
x.Value = UCase(x.value)
Next
End Sub

Can anyone fix this for me?
thanx - chu

 

[Dave Peterson]

If you can select the range first, it might make it simpler:

Sub Uppercase()
Dim x as Range
' Loop to cycle through each cell in the specified range.
For Each x In Selection.Cells
' Change the text in the range to uppercase letters.
x.Value = UCase(x.value)
Next
End Sub

 

[chubach]

Thanks, it works great. However, if I select the entire workbook by clicking
in the upper, lefthand part of the row/column headings it causes the
application to freeze or keep looping. Is there a way to make the selection
this way and have it stop after the last active cell?

 

[Dave Peterson]

You could limit the loop to just the usedrange, but if you're going to limit
them, you might as well just loop through the cells that have text constants.

Option Explicit
Sub Uppercase()
Dim x As Range
Dim myRng As Range

Set myRng = Nothing
On Error Resume Next
Set myRng = Intersect(Selection, _
Selection.Cells.SpecialCells(xlCellTypeConstants, xlTextValues))
On Error GoTo 0

If myRng Is Nothing Then
MsgBox "no constants selected"
Else
' Loop to cycle through each cell in the specified range.
For Each x In Selection.Cells
' Change the text in the range to uppercase letters.
x.Value = UCase(x.Value)
Next x
End If
End Sub