Remove Trailing Space through VBA

In case you want a VBA code to remove all the trailing spaces in only active sheet then this article will definitely help you. Many times we left a space at the end in error or sometimes we copy data from web & do not notice. In this article, we will learn how to eliminate the space left in error.

Question): In Excel, we can use TRIM function with SUBSTITUTE & CHAR i.e. =TRIM(SUBSTITUTE(A1,CHAR(160)," ")) to remove trailing space at the end of any text.

I would like a Macro to help me in removing all the unnecessary spaces left at the end of the string.

To get the code for removing space at end of the text; we need to follow the below steps to launch VB editor.

  • Click on Developer tab
  • From Code group, select Visual Basic

 

img1

 

Copy the following code in worksheet module

 

Sub trimspace()

    Dim c As Range, rngConstants As Range

    On Error Resume Next

    Set rngConstants = ActiveSheet.UsedRange.SpecialCells(2, 2)

    On Error GoTo 0

    If Not rngConstants Is Nothing Then

        'optimize performance

        Application.ScreenUpdating = False

        Application.Calculation = xlCalculationManual

        'trim cells incl char 160

        For Each c In rngConstants

            c.Value = Trim$(Application.Clean(Replace(c.Value, Chr(160), " ")))

        Next c

        'reset settings

        Application.ScreenUpdating = True

        Application.Calculation = xlCalculationAutomatic

    End If

End Sub

 

img2

 

  • This will remove all the spaces at the end.

Note: The above macro will run only on activesheet.

In this way, we can remove the trailing spaces at the end, using VBA code.

 

image 4

Download - Remove Trailing space through VBA - xlsm

Users are saying about us...

  1. Hi i have a workbook with 4 sheets i would like to update data from sheet1 to sheet4 but the cells in sheet one has a different column (B1,c3,d2,f3,e1,f5,c9 and d9) and sheet4 start b7, c7,d7,e7,f7,g7,h7 and i7 could please give me the vba code.

Leave a Reply

Your email address will not be published. Required fields are marked *

Terms and Conditions of use

The applications/code on this site are distributed as is and without warranties or liability. In no event shall the owner of the copyrights, or the authors of the applications/code be liable for any loss of profit, any problems or any damage resulting from the use or evaluation of the applications/code.

Visit Us On TwitterVisit Us On FacebookVisit Us On Youtube