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

 

[Andresuru]

Basically I added the VBA code provided by joe in a Module called TrimXP so I added a button in "Main" Sheet and I assigned the Macro, when I trie to run from "Main" Sheet appears and error in data Dictionary that it suppose that should be run correctly.

When I run the macro from Macro Project view, it runs well.

You can check the images as below.

Hope this helps

 

[JoeMo]

What sheet do you want the code to address? What sheet is the button on?

If the button is not on the sheet you want the code to address, then you must modify the code by introducing sheet name qualifiers. The code I posted is designed to run on the sheet that is active.

 

[johnnyL]

Something like this:

Sub RemoveLeadingAndTrailingSpaces()
'
    Dim V As Variant, i As Long
    Dim WS              As Worksheet
'
    Set WS = Sheets("Sheet1")                                                       ' <--- Set this to the sheet name
'
    V = WS.Range("B1:B" & WS.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
'
    WS.Range("B1:B" & WS.Cells(Rows.Count, "B").End(xlUp).Row).Value = V
End Sub

 

[Peter_SSs]

As has been mentioned, we don't know which sheet the data to be trimmed is on but in any case, here is a way that should trim them all at once (after you have edited the target sheet name in the code if required)

This does also differ from the other suggested codes in that if there are texts with multiple consecutive internal spaces, these would be reduced to single spaces.

Sub TrimIt()
  With Sheets("Data Dictionary")  '<- Edit target sheet name if required
    With .Range("B1", .Range("B" & Rows.Count).End(xlUp))
      .Value = Evaluate("trim(" & .Address(External:=True) & ")")
    End With
  End With
End Sub

 

[Andresuru]

What sheet do you want the code to address? What sheet is the button on?

If the button is not on the sheet you want the code to address, then you must modify the code by introducing sheet name qualifiers. The code I posted is designed to run on the sheet that is active.

What sheet do you want the code to address? What sheet is the button on?
The code should be executed in "Data Dictionary" Sheet and the button is in "Main" Sheet

 

[Peter_SSs]

The code should be executed in "Data Dictionary" Sheet and the button is in "Main" Sheet

So, did you try the post #14 code?

 

[JoeMo]

What sheet do you want the code to address? What sheet is the button on?
The code should be executed in "Data Dictionary" Sheet and the button is in "Main" Sheet

Here's a modification to the code I posted that will allow you to run it from a button on any sheet in the workbook:

Sub RemoveLeadingAndTrailingSpaces()
Dim V As Variant, i As Long
With Sheets("Data Dictionary")
    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 With
End Sub

 

[Andresuru]

Here's a modification to the code I posted that will allow you to run it from a button on any sheet in the workbook:

Sub RemoveLeadingAndTrailingSpaces()
Dim V As Variant, i As Long
With Sheets("Data Dictionary")
    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 With
End Sub

Hi Joe, Very Good Job! THANK YOU A LOT

You can close this thread as completed or resolved.

 

[JoeMo]

Hi Joe, Very Good Job! THANK YOU A LOT

You can close this thread as completed or resolved.

You are welcome- thanks for the reply.

 

[Andresuru]

You are welcome- thanks for the reply.

Hello @JoeMo ,

Using your VBA code as reference, please note there is a small request that i would like to ask.

New change : How should I put the VBA code that I need to start trimming from row 2 to the last row <> empty but when the ACTIVE column is selected so the user can trim the cells from any worksheets and any column.

Sub TRIMPRO()

With Application

.ScreenUpdating = False
.DisplayAlerts = False
.AskToUpdateLinks = False

Dim V As Variant, i As Long
V = Range(ActiveCell.Column, Cells(Rows.Count, Activecolumn).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(ActiveCell.Column, Cells(Rows.Count, Activecolumn).End(xlUp).Row).Value = V
    
End With
End Sub

Appreciate a lot your help
Appreciate a lot if you provide any any paid course regarding VBA.

Regards
Andres