0

I am trying to run this Sub on an excel sheet, single column, 6559 rows of the same 3 repeating 7 digit numbers, using VBA, but it becomes unresponsive. I am not selecting the entire column, only 6559 rows from one column. There are no formulas. Is this normal? What am I doing wrong?

Sub TrimWhiteSpace ()
   Dim rng as Range
   Set rng as Selection
   For Each cell In rng
      cell.Value = Trim(cell)
   Next cell
End sub

I have tried using trim as both Trim and Trim$.

i7 8cores 32gb ram SSD. Office365 Desktop.

JvdV
  • 70,606
  • 8
  • 39
  • 70
talkinggoat
  • 163
  • 1
  • 1
  • 9
  • Probably not related to your issue but you can trim a whole range at once. Possibly faster than looping cells? [https://stackoverflow.com/a/58454080/9758194](https://stackoverflow.com/a/58454080/9758194) – JvdV Feb 02 '22 at 23:31
  • (^^^ note that `Application.Trim` behaves slightly different than `Trim` but definitely suggest you use the former). – BigBen Feb 02 '22 at 23:50
  • This one would be fast (and if you want to just trim leading/trailing spaces) https://stackoverflow.com/a/46278839/478884 – Tim Williams Feb 03 '22 at 00:07
  • May be try this: `cell.Value = Trim(Cstr(cell.Value))` – Charlie Feb 03 '22 at 02:45
  • Check [this article](https://www.soa.org/news-and-publications/newsletters/compact/2012/january/com-2012-iss42/excel-vba-speed-and-efficiency/) – Sgdva Feb 03 '22 at 02:48

1 Answers1

0

Interacting of macros with sheet cells is time-consuming. Consider the following code. It takes your Selection to array. Modify it and then paste back. So your macros refers to sheet 2 times only instead of thousands times as in your code.

Sub TrimWhiteSpace()
   Dim rng As Range
   Set rng = Selection
   
'   For Each cell In rng
'      cell.Value = Trim(cell)
'   Next cell
    
    Dim rangeArray, i As Long, j As Long
    'Note that if Selection is single cell you'll get an error.
    'Add handling of single cell selection
    
    'put range to array
    rangeArray = rng.Value
    'work with array in memory
    For i = 1 To UBound(rangeArray, 1)
        'If Selection is always one column remove nested j-loop
        'rangeArray(i, 1) = Trim(rangeArray(i, 1))
        For j = 1 To UBound(rangeArray, 2)
            rangeArray(i, j) = Trim(rangeArray(i, j))
        Next
    Next
    'paste back array to range
    rng.Value = rangeArray
End Sub

As in @JvdV link this code will replace any formulas by values. But you told your range doesn't contains formulas.

Oleksandr Pavlov
  • 83
  • 1
  • 6