3

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
halfer
  • 19,824
  • 17
  • 99
  • 186
Alejandro Villalon
  • 63
  • 1
  • 1
  • 5

3 Answers3

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)
basodre
  • 5,720
  • 1
  • 15
  • 23
  • `Trim`ming a range does not work. You have to Trim cell by cell – iDevlop Aug 15 '15 at 13:29
  • @iDevlop Give it a try. Using `Application.Trim` (as opposed to the WorksheetFunction or just `Trim`) allows you to trim a range. – basodre Aug 17 '15 at 14:49
  • 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 ;-) – iDevlop Aug 17 '15 at 16:11
  • @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. – basodre Aug 17 '15 at 17:20
  • This is a nice piece of useful VBA. Thank you. – Miha Trošt Dec 09 '16 at 09:03
8

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.

Alex K.
  • 171,639
  • 30
  • 264
  • 288
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
99moorem
  • 1,955
  • 1
  • 15
  • 27