0

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?

Neran
  • 55
  • 7
  • You could use an array or turn off [screen updating](https://stackoverflow.com/questions/47089741/how-to-speed-up-vba-code/47092175#47092175), re-calculation etc. – Storax Feb 20 '22 at 19:38

2 Answers2

2

This will copy the values into an array, do the ucase and write it back. In such a way you reduce the number of read and write accesses to the sheet which usually slows down the code as every write access might trigger events, recalculations or cause a screen updating.

Private Sub Worksheet_Activate()

    On Error GoTo EH
    Application.EnableEvents = False
    
    Dim rg As Range
    Set rg = Range("$G$6:$G$200")
    
    Dim vdat As Variant
    vdat = rg.Value
    
    Dim i As Long, j As Long
    For i = LBound(vdat, 1) To UBound(vdat, 1)
        For j = LBound(vdat, 2) To UBound(vdat, 2)
            vdat(i, j) = UCase(vdat(i, j))
        Next
    Next
    
    rg.Value = vdat

EH:
    Application.EnableEvents = True
    
End Sub
Storax
  • 11,158
  • 3
  • 16
  • 33
  • Thanks. Doing the operation using the array solved this. Just disabling screenupdate or events did not help much. – Neran Feb 20 '22 at 22:34
0

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 & ")")
FaneDuru
  • 38,298
  • 4
  • 19
  • 27