Trim Cells using VBA in Excel

Question

I have what seems like a simple problem with some data in Excel. I have a lot of data with leading spaces pasted from a web table, and I would like to get rid of the initial space. I cribbed the following code (I am completely new to VBA), but it doesn't seem to work. When I step through it in the debugger it looks like an infinite loop. Any help would be appreciated!

Sub DoTrim()
  For Each cell In Selection.Cells
    If cell.HasFormula = False Then
      cell = Trim(cell)
    End If
  Next
End Sub

EDIT: It does look like the TRIM function is running into problems with a "space" character. This code:

Sub DoTrim()
Dim cell As Range, areaToTrim As Range
Set areaToTrim = Selection.Cells
For Each cell In areaToTrim
    cell.Value = "MUPPET"
Next cell
End Sub

Changed the value of the cells, so I guess it's a non-space whitespace! Any idea on how to get rid of those?

Answer 1

This works well for me. It uses an array so you aren't looping through each cell. Runs much faster over large worksheet sections.

Sub Trim_Cells_Array_Method()

Dim arrData() As Variant
Dim arrReturnData() As Variant
Dim rng As Excel.Range
Dim lRows As Long
Dim lCols As Long
Dim i As Long, j As Long

  lRows = Selection.Rows.count
  lCols = Selection.Columns.count

  ReDim arrData(1 To lRows, 1 To lCols)
  ReDim arrReturnData(1 To lRows, 1 To lCols)

  Set rng = Selection
  arrData = rng.value

  For j = 1 To lCols
    For i = 1 To lRows
      arrReturnData(i, j) = Trim(arrData(i, j))
    Next i
  Next j

  rng.value = arrReturnData

  Set rng = Nothing
End Sub