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.
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
Note: The above macro will run only on activesheet.
In this way, we can remove the trailing spaces at the end, using VBA code.
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.
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.