Trim Cells

PHOTO EMBED

Wed Sep 08 2021 04:54:28 GMT+0000 (Coordinated Universal Time)

Saved by @cnewnham #vba

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
content_copyCOPY