CODEWITHSUNDEEP
vbaexcel
JimJimL
JimJimL

Reputation: 129

Trim a cell with VBA in a loop

I'm trying to use the trim function without success. After searching for the solution on this forum and other sources online I have seen many different approaches.

Is there no simple way of trimming a cell in VBA?

What I want is something like this:

Sub trimloop()

Dim row As Integer

row = 1

Do While Cells(row, 1) <> ""

   Cells(row, 2) = trim(Cells(row, 2))

   row = row + 1

Loop

So that when there is a value in column A (1) the value in column B (2) should be trimmed of any extra spaces. I just cant get this to work for me.

Appreciate any help/tips!

Regards Jim

Upvotes: 0

Views: 3452

Answers (4)

user2829825
user2829825

Reputation: 1

Don't know if this overly simplified... but thought I would simply throw it out there this worked for me. The only predecessor step is you assign a "named range" to your workbook/worksheet/dataset ... name a data set and then iterate over the data set with this code 

Sub forEachLoop()
    For Each cell In Range("yourNamedRange")
        cell.Value = Trim(cell.Value)
    Next cell
End Sub

Upvotes: 0

Patrick Lepelletier
Patrick Lepelletier

Reputation: 1654

this is the optimized version of your code, in case of big data sheets:

Option Explicit

Sub trimloop()

Dim row As Long, max As Long
Dim Data() As Variant

With ThisWorkbook.Sheets(1)

    max = .Cells(1, 1).End(xlDown).row 'this does the same as your code, on first empty cell it stops
    'the following finds the last un-empty cell of column(1):
    'max= .cells(.rows.count,1).end(xlup).row

    'copies values from sheet to memory (is faster for working with later)
    Data = .Range(.Cells(1, 1), .Cells(max, 2)).Value2

    'loop :
    For row = 2 To max + 1

        'work with memory instead of sheet
        Data(row, 2) = Trim(Data(row, 2))
        'for complete delete of all spaces use : = replace( StringName," ", "")

    Next row

    'write back to sheet
    .Range(.Cells(1, 1), .Cells(max, 2)).Value2 = Data

End With

erase Data 'free memory

End Sub

Upvotes: 1

Luboš Suk
Luboš Suk

Reputation: 1546

So i made the code a bit accurate and mistakeproof and it worked.

So i can recommend you to double check, if you have correct row and column values, because you probably targeting wrong cells. (cause your code is working)

Sub trimloop()

Dim row As Integer
Dim currentSheet As Worksheet
Set currentSheet = sheets("Sheet1")

row = 2

Do While currentSheet.Cells(row, 1) <> ""

   currentSheet.Cells(row, 2).Value = Trim(currentSheet.Cells(row, 2).Value)

   row = row + 1

Loop

End Sub

Upvotes: 2

Brendan Gooden
Brendan Gooden

Reputation: 1551

Use Application.WorksheetFunction.Trim(string)

Sub trimloop()

Dim row As Integer

row = 1

With ThisWorkbook.ActiveSheet
Do While .Cells(row, 1) <> ""

   .Cells(row, 2) = Application.WorksheetFunction.Trim(.Cells(row, 2))

   row = row + 1

Loop
End With
End Sub

Upvotes: 1

Related Questions