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?
Example. This example uses the LTrim function to strip leading spaces, and the RTrim function to strip trailing spaces from a string variable. It uses the Trim function to strip both types of spaces.
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
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With