How to remove spaces from an entire Excel column using VBA?

Question

I'm trying to delete the spaces that I have in each field of the column "A" this spaces are at the end of the string some of the string has 3 spaces others 4. When I run the code, I don't have error, so I think I have a bug because nothing happened when is running.

Dim result As String
Last = Cells(Rows.Count, "A").End(xlUp).Row
    For i = Last To 1 Step -1
      If (Right(Cells(i, "A").Value, 4)) Like "    " Or (Right(Cells(i, "A").Value, 3)) Like "   " Then
        result = Replace(Cells(i, "A"), " ", "")
    End If
Next i

basodre · Accepted Answer · 2015-08-17 17:20:02Z

12

In your specific case, the problem is that you're storing the replacement value in a string variable named result, then doing nothing with it. If you want it to be in the Cell, you have to add it back in there, such as:

Cells(I, "A").Value = result

Keep in mind, there is an Application.Trim method that can actually save a bit of time over looping. Experiment with code such as:

Dim rng as Range

set rng = Range("A1:A10")
rng.Value = Application.Trim(rng)

edited Aug 17, 2015 at 17:20

answered Aug 14, 2015 at 15:44

basodre

5,7901 gold badge18 silver badges25 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Patrick Honorez Over a year ago

Trimming a range does not work. You have to Trim cell by cell

basodre Over a year ago

@iDevlop Give it a try. Using Application.Trim (as opposed to the WorksheetFunction or just Trim) allows you to trim a range.

Patrick Honorez Over a year ago

Well. I did try and got an error, before posting. But now I realize that it was because I copied your code which has an error on line 3: )" instead of "). So edit your code so I can change the downvote into an upvote ;-)

basodre Over a year ago

@iDevlop Whoops, and thanks for the heads up! I occasionally type code responses directly into the website (without testing) for easier blocks of code, and I missed that.

Alex K. · Accepted Answer · 2015-08-14 15:45:40Z

9

Currently you are not actually updating the cell in the loop, you just;

result = Replace(Cells(i, "A"), " ", "")

You should:

Cells(i, "A") = Replace(Cells(i, "A"), " ", "")

Or better

Cells(i, "A") = rtrim$(Cells(i, "A"))

Which will remove all right spaces. You can probably remove the if check as well.

answered Aug 14, 2015 at 15:45

Alex K.

177k32 gold badges276 silver badges299 bronze badges

2 Comments

Patrick Honorez Over a year ago

The $ at the end is really old school ! Does it have an added value ?

Alex K. Over a year ago

Yes; its the version of the function that actually returns a string, without it you get an unnecessary variant

99moorem · Accepted Answer · 2015-08-14 15:48:30Z

0

What are expecting this code to do? as currently the values are being read into result. It would also be better to use trim to remove trailing spaces.

Dim result As String
Last = Cells(Rows.Count, "A").End(xlUp).Row
    For i = Last To 1 Step -1
        result = RTrim(Cells(i, "A"))
    Next i

edited Aug 14, 2015 at 15:48

answered Aug 14, 2015 at 15:44

99moorem

1,9931 gold badge16 silver badges28 bronze badges

1 Comment

Alejandro Villalon Over a year ago

Thanks guys for your quick response

Collectives™ on Stack Overflow

How to remove spaces from an entire Excel column using VBA?

3 Answers 3

4 Comments

2 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

4 Comments

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related