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

Leave a Reply

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

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

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 Google PlusVisit Us On Youtube