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]

The code you posted has errors in it. I tried to fix them and made some modifications so the user can first select an entire column, then run the code and it will trim from row 2 to the last filled row in the selected column.

Sub TRIMPRO()
Dim V As Variant, i As Long
With Application
    .ScreenUpdating = False
    .DisplayAlerts = False
    .AskToUpdateLinks = False
    V = Range(Cells(2, ActiveCell.Column), Cells(Rows.Count, ActiveCell.Column).End(xlUp)).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(Cells(2, ActiveCell.Column), Cells(Rows.Count, ActiveCell.Column).End(xlUp)).Value = V
.ScreenUpdating = True
.DisplayAlerts = True
.AskToUpdateLinks = True
End With
End Sub

 

[Andresuru]

The code you posted has errors in it. I tried to fix them and made some modifications so the user can first select an entire column, then run the code and it will trim from row 2 to the last filled row in the selected column.

Sub TRIMPRO()
Dim V As Variant, i As Long
With Application
    .ScreenUpdating = False
    .DisplayAlerts = False
    .AskToUpdateLinks = False
    V = Range(Cells(2, ActiveCell.Column), Cells(Rows.Count, ActiveCell.Column).End(xlUp)).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(Cells(2, ActiveCell.Column), Cells(Rows.Count, ActiveCell.Column).End(xlUp)).Value = V
.ScreenUpdating = True
.DisplayAlerts = True
.AskToUpdateLinks = True
End With
End Sub

I have been spending like 2 hours today trying to get results.
Wonderful , works great. Thanks Again

 

[JoeMo]

I have been spending like 2 hours today trying to get results.
Wonderful , works great. Thanks Again

You are welcome - thanks for the reply.

 

[Andresuru]

The code you posted has errors in it. I tried to fix them and made some modifications so the user can first select an entire column, then run the code and it will trim from row 2 to the last filled row in the selected column.

Sub TRIMPRO()
Dim V As Variant, i As Long
With Application
    .ScreenUpdating = False
    .DisplayAlerts = False
    .AskToUpdateLinks = False
    V = Range(Cells(2, ActiveCell.Column), Cells(Rows.Count, ActiveCell.Column).End(xlUp)).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(Cells(2, ActiveCell.Column), Cells(Rows.Count, ActiveCell.Column).End(xlUp)).Value = V
.ScreenUpdating = True
.DisplayAlerts = True
.AskToUpdateLinks = True
End With
End Sub

Hi @JoeMo , Checking this VBA code with other person it looks the VBA code is not working with excel 365 32 bits only for 64 bit.
How should be the code for version 32 bit?

Appreciate your response
Thanks

 

[JoeMo]

Hi @JoeMo , Checking this VBA code with other person it looks the VBA code is not working with excel 365 32 bits only for 64 bit.
How should be the code for version 32 bit?

Appreciate your response
Thanks

I'm using 32 bit excel with MS 365 and the code is working. Below from File>Account>About Excel:
Microsoft® Excel® for Microsoft 365 MSO (Version 2201 Build 16.0.14827.20028) 32-bit

Can you tell me what exactly is "not working" for you? Can't compile or you get a run time error, or what ....?

 

[Andresuru]

I'm using 32 bit excel with MS 365 and the code is working. Below from File>Account>About Excel:
Microsoft® Excel® for Microsoft 365 MSO (Version 2201 Build 16.0.14827.20028) 32-bit

Can you tell me what exactly is "not working" for you? Can't compile or you get a run time error, or what ....?

Hi @JoeMo ,

Apologies for the delayed,

The error is in the image attached.

Regards
Andres

 

[Rick Rothstein]

Do you have a module that you named "Trim"? If so, that is the problem (you should not use VBA keywords for the names of modules, variable, functions, etc.). You should be able to get around the problem by changing that line of code to this...

V(i, 1) = VBA.Trim(V(i, 1))

It would be better to rename the module instead though.

 

[Andresuru]

Do you have a module that you named "Trim"? If so, that is the problem (you should not use VBA keywords for the names of modules, variable, functions, etc.). You should be able to get around the problem by changing that line of code to this...

V(i, 1) = VBA.Trim(V(i, 1))

It would be better to rename the module instead though.

Hi Rick,

Actually that was the issue, I thought my coworkers added the module with different name, thanks a lot.

Regards
Andres