2

I want to convert to upper case a selected range in Excel. Here's my code

Dim rng As Range
Set rng = Selection
rng.Value = rng.Parent.Evaluate("INDEX(UPPER(" & rng.Address & "),)")

It works on a large range, but gives the #VALUE! error when only a single cell is selected.

PBeezy
  • 1,222
  • 2
  • 17
  • 26

2 Answers2

3
  1. Cut your selection down to the used range in case you have selected an entire column or row.

  2. Process a single cell if you only have a single cell selected; process in bulk for any multiple cell selections.

    Dim rng As Range
Set rng = Intersect(Selection, Selection.Parent.UsedRange)
If rng.Cells.Count > 1 Then
    rng.Value = Application.Evaluate("INDEX(UPPER(" & rng.Address & "),)")
Else
    rng = UCase(rng.Value)
End If
0

This code will work for single cell or a selection:

Sub ChangeCellCase()

Dim CellCase

   For Each CellCase In Selection
      CellCase.Value = UCase(CellCase.Value)
   Next

End Sub
5202456
  • 966
  • 14
  • 24
  • Beware that this may take some time if applying it to a lot of cells. Would be faster to take entire range into memory, convert it to uppercase, then write it back to the sheet one time. If the use case is a small number of cells then this may not be worth looking into. – JG7 Feb 27 '18 at 00:02