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

Works fine for me with one change - fourth line should be:

cell.Value = Trim(cell.Value)

---

Edit: If it appears to be stuck in a loop, I'd add

Debug.Print cell.Address

inside your For ... Next loop to get a bit more info on what's happening.

I also suspect that Trim only strips spaces - are you sure you haven't got some other kind of non-display character in there?

Answer 2

The infinite loop is a result of an On Error Resume Next statement somewhere higher up in your code. Get rid of it now. If your code only runs with On Error Resume Next in effect, it needs instant and complete overhaul.

And while you are at it, place an Option Explicit on top of yor module. It forces you to declare all variables, a safeguard against annoying bugs that are the result of mis-typed variable names. (You can modify the VBA editor preferences to have that option set automatically the next time, which is highly recommended.)

The immediate problem with your code is that a cell is an object, and objects cannot be trimmed. You want to trim the text of the cell, so you must do so:

cell.Text = Trim(cell.Text)

Answer 3

This should accomplish what you want to do. I just tested it on a sheet of mine; let me know if this doesn't work. LTrim is if you only have leading spaces; the Trim function can be used as well as it takes care of leading and trailing spaces. Replace the range of cells in the area I have as "A1:C50" and also make sure to change "Sheet1" to the name of the sheet you're working on.

Dim cell As Range, areaToTrim As Range
Set areaToTrim = Sheet1.Range("A1:C50")
For Each cell In areaToTrim
    cell.Value = LTrim(cell.Value)
Next cell

Answer 4

I would try to solve this without VBA. Just select this space and use replace (change to nothing) on that worksheet you're trying to get rid off those spaces.

If you really want to use VBA I believe you could select first character

strSpace = left(range("A1").Value,1)

and use replace function in VBA the same way

Range("A1").Value = Replace(Range("A1").Value, strSpace, "")

or

for each cell in selection.cells
 cell.value = replace(cell.value, strSpace, "")
next

Answer 5

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