Macro Loop through cells in column

[jakobt]

Have a column C with a lot of data. Many of the cells contain spaces in the end. I want to trim each cell by using a macro looping through each cell.
I want to practice looping.
Is it possible to write a code which loops through each cell and removes the space(s) in each cell.

Thanks.

 

[tonyyy]

jakobt,

You might consider the following...

Sub anotherLoop()
Dim r As Range
For Each r In Range("C1:C" & Cells(Rows.Count, "C").End(xlUp).Row)
    r.Value = Trim(r.Value)
Next r
End Sub

Cheers,

tonyyy

 

[hiker95]

jakobt,

Here is another macro solution for you to consider that does not do any looping thru the cells in column C.

Please test the macro in a copy of your workbook.

Sub RemoveSpaceCharacterFromEndOfCell()
' hiker95, 07/16/2018, ME1063114
Dim Addr As String
Addr = "C2:C" & Cells(Rows.Count, "C").End(xlUp).Row
Range(Addr) = Evaluate(Replace("IF(Right(@,1)="" "",LEFT(@,LEN(@)-1),@)", "@", Addr))
End Sub

 

[hiker95]

jakobt,

The macro in my last reply will only remove the last space character in the string, and, NOT all trailing space characters.

I will be back later today after checking my archives.

I hope that Rick Rothstein will respond to this thread with one of his magical Range(Addr) = Evaluate(Replace( solutions.

 

[Peter_SSs]

See if this does what you want.

Sub RemoveSpaces()
  With Range("C2", Range("C" & Rows.Count).End(xlUp))
    .Value = Evaluate(Replace("if(#="""","""",trim(#))", "#", .Address))
  End With
End Sub

 

[hiker95]

Peter,

Nicely done, and, one for my archives.