Remove Spaces in the beginning of the cell and the end (TRIM)

[Andresuru]

Hello Team,

I was wondering if someone can help with a VBA code, I dont have experience using trim to remove blank spaces at the beginning and the end of the cells.

Expectation: Basically i need to remove blank spaces (Beginning & the end) in each cell with strings in column B but only the cells that are not empty until last row that has values.
I guess the loop needs to be from Activecell to LastRow with values.

I appreciate a lot if you can help since I need to add this VBA code to another macro so data can be look friendly and well.
Please let me know if you need an excel file or photo about the expectation.

Note: I already checked other threads but I cannot find it, appreciate help me on this
Regards

 

[JoeMo]

This assumes that the cells do not hold formulas and looks from B1 to last filled row in col B, adjust to suit:

Sub RemoveLeadingAndTrailingSpaces()
Dim V As Variant, i As Long
V = Range("B1:B" & Cells(Rows.Count, "B").End(xlUp).Row).Value
For i = 1 To UBound(V, 1)
    If V(i, 1) <> "" Then
        V(i, 1) = Trim(V(i, 1))
    End If
Next i
Range("B1:B" & Cells(Rows.Count, "B").End(xlUp).Row).Value = V
        
End Sub

 

[Andresuru]

This assumes that the cells do not hold formulas and looks from B1 to last filled row in col B, adjust to suit:

Sub RemoveLeadingAndTrailingSpaces()
Dim V As Variant, i As Long
V = Range("B1:B" & Cells(Rows.Count, "B").End(xlUp).Row).Value
For i = 1 To UBound(V, 1)
    If V(i, 1) <> "" Then
        V(i, 1) = Trim(V(i, 1))
    End If
Next i
Range("B1:B" & Cells(Rows.Count, "B").End(xlUp).Row).Value = V
       
End Sub

Hi Joe, Its getting and error , can you please check?

Regards

 

[JoeMo]

Hi Joe, Its getting and error , can you please check?

View attachment 49226

Regards

From your image there appears to be a space between Trim and the left parentheses. Suggest you copy the code in post #2 directly from your browser and paste it directly into the VBE window.

 

[Dan_W]

Hi Andresuru, Joe.
just stumbled across this thread looking for something else and was intrigued by the code solution. So Joe's code works perfectly for me (Thank you, Joe - this looks very useful!), so I can confirm that the problem isn't with the code. Looking at the screen capture that you helpfully provided, Andresuru, I suspect another problem might be with how you've opted to name your code module. It appears that you've called the active module "TRIM" - you really should avoid using VBA keywords, etc when naming modules and variables, etc - other wise VBA will get confused. Try changing the name of the module too and see how it goes.

 

[johnnyL]

I agree, the module called 'Trim' would cause that problem. Rename that module and try again.

 

[Andresuru]

Hi Andresuru, Joe.
just stumbled across this thread looking for something else and was intrigued by the code solution. So Joe's code works perfectly for me (Thank you, Joe - this looks very useful!), so I can confirm that the problem isn't with the code. Looking at the screen capture that you helpfully provided, Andresuru, I suspect another problem might be with how you've opted to name your code module. It appears that you've called the active module "TRIM" - you really should avoid using VBA keywords, etc when naming modules and variables, etc - other wise VBA will get confused. Try changing the name of the module too and see how it goes.

Hello Dan you are correct, I changed and that fix the issue.

 

[Andresuru]

This assumes that the cells do not hold formulas and looks from B1 to last filled row in col B, adjust to suit:

Sub RemoveLeadingAndTrailingSpaces()
Dim V As Variant, i As Long
V = Range("B1:B" & Cells(Rows.Count, "B").End(xlUp).Row).Value
For i = 1 To UBound(V, 1)
    If V(i, 1) <> "" Then
        V(i, 1) = Trim(V(i, 1))
    End If
Next i
Range("B1:B" & Cells(Rows.Count, "B").End(xlUp).Row).Value = V
        
End Sub

Thank you a lot for this vba Code, it works perfectly, I have a question I could not run through a button in another sheet do you know the reason ?

 

[johnnyL]

Thank you a lot for this vba Code, it works perfectly, I have a question I could not run through a button in another sheet do you know the reason ?

Does it give error, improper results? What does "could not run through a button in another sheet" mean? Please provide more info.

 

[JoeMo]

Thank you a lot for this vba Code, it works perfectly, I have a question I could not run through a button in another sheet do you know the reason ?

You are welcome - thanks for the reply.

Show the code you used so we can see if you correctly qualified the sheet you want to execute the code on.