Excel - slow VBA execution of simple loop

Question

I just want to make sure that text inside a column is changed into uppercase.

For this I am using the following code

Private Sub Worksheet_Activate()

Dim cell As Range

For Each cell In Range("$G$6:$G$200")
    cell.Value = UCase(cell.Value)
Next cell

End Sub

In this case the loop runs over ~200 cells but it already takes about 15 seconds to execute. Any ideas?

Answer 1

Iterating between cells take times. Using an array and dropping the result at the end, iteration will be much faster and writing in each iteration makes the code slower. But Evaluate is more compact and has a similar efficiency:

  Dim rng As Range: Set rng = Range("$G$6:$G$200")
  rng.value = Evaluate("Upper(" & rng.Address & ")")