Crop 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 inserted from a web spreadsheet and I would like to get rid of the initial space. I backed up the following code (I'm completely new to VBA), but it doesn't seem to work. When I exit through it in the debugger, it looks like an endless 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 looks like the TRIM function is encountering problems with the 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

The value of the cells has been changed, so I assume this is a space without spaces! Any idea on how to get rid of them?

Answer 1

,

Option Explicit
Sub DoTrim()
    Dim cell As Range
    Dim str As String
    Dim nAscii As Integer
    For Each cell In Selection.Cells
        If cell.HasFormula = False Then
            str = Trim(CStr(cell))
            If Len(str) > 0 Then
                nAscii = Asc(Left(str, 1))
                If nAscii &lt 33 Or nAscii = 160 Then
                    If Len(str) &gt 1 Then
                        str = Right(str, Len(str) - 1)
                    Else
                        strl = ""
                    End If
                End If
            End If
            cell=str
        End If
    Next
End Sub

Answer 2

. , . .

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

Answer 3

- :

cell.Value = Trim(cell.Value)

---

: ,

Debug.Print cell.Address

For ... Next, , .

, Trim - , ?

Answer 4

, :

. /: P.

Sub TrimSelected()    
Dim rng As Range, cell As Range    
Set rng = Selection   

For Each cell In rng    
cell = Trim(cell)   

Next cell    

End Sub

Answer 5

On Error Resume Next - . . On Error Resume Next, .

, Option Explicit yor. , , . ( VBA, , .)

, , . , :

cell.Text = Trim(cell.Text)

Answer 6

, . ; , . LTrim - ; Trim, . , "A1: C50", "Sheet1" , .

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 7

I would try to solve this without VBA. Just select this space and replace (don't change anything) on ​​this sheet that you are trying to get rid of these spaces.

If you really want to use VBA, I believe that you can select the first character

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

and use the replace function in VBA in 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 8

The only thing that worked for me was this function:

Sub DoTrim()
Dim cell As Range
Dim str As String
For Each cell In Selection.Cells
    If cell.HasFormula = False Then
        str = Left(cell.Value, 1) 'space
        While str = " " Or str = Chr(160)
            cell.Value = Right(cell.Value, Len(cell.Value) - 1)
            str = Left(cell.Value, 1) 'space
        Wend
        str = Right(cell.Value, 1) 'space
        While str = " " Or str = Chr(160)
            cell.Value = Left(cell.Value, Len(cell.Value) - 1)
            str = Right(cell.Value, 1) 'space
        Wend
    End If
Next cell
End Sub