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

Andresuru

New Member
Joined
Sep 6, 2021
Messages
25
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Sep 6, 2021
Messages
25
Office Version
  1. 365
Platform
  1. Windows
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
 

Attachments

  • Error.JPG
    Error.JPG
    69.2 KB · Views: 6
  • Macro in Module.JPG
    Macro in Module.JPG
    72.5 KB · Views: 6
  • Remove Spaces in Data Dictionary.JPG
    Remove Spaces in Data Dictionary.JPG
    65.9 KB · Views: 6

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.

JoeMo

MrExcel MVP
Joined
May 26, 2009
Messages
17,760
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
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

Well-known Member
Joined
Nov 7, 2011
Messages
2,131
Office Version
  1. 2013
  2. 2007
Platform
  1. Windows
Something like this:

VBA Code:
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

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
52,372
Office Version
  1. 365
Platform
  1. Windows
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.

VBA Code:
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

New Member
Joined
Sep 6, 2021
Messages
25
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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
 

JoeMo

MrExcel MVP
Joined
May 26, 2009
Messages
17,760
Office Version
  1. 365
  2. 2010
Platform
  1. Windows

ADVERTISEMENT

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:
VBA Code:
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
 
Solution

Andresuru

New Member
Joined
Sep 6, 2021
Messages
25
Office Version
  1. 365
Platform
  1. Windows
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:
VBA Code:
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.
 

Andresuru

New Member
Joined
Sep 6, 2021
Messages
25
Office Version
  1. 365
Platform
  1. Windows
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.

VBA Code:
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
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,152,566
Messages
5,770,893
Members
425,651
Latest member
Mark Cashin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top