Syntax of the VBA TRIM function
There is a TRIM function in VBA as well (and it does exactly the same thing – remove extra spaces).
Why do we need a TRIM function in VBA too?
Fair question!
While you can work with the inbuilt TRIM function in Excel worksheets to get rid of spaces. in some cases, you may need to do this using VBA.
This could be as a part of a bigger code.
Syntax of the VBA TRIM function
TRIM(String)
‘String’ is the argument that can be a text string or a variable that holds a text string.
Let’s learn how to use the VBA TRIM function with a couple of examples.
Example 1 – Showing Result in a message box
Suppose you have the following text in cell A1

Note that there are spaces before and after the sentence.
The below code would take this sentence, and show the result after trimming the extra spaces
Sub TrimExample1() MsgBox Trim(Range("A1")) End Sub

There is one important thing to know when using the TRIM function in VBA – it will only remove the leading and trailing spaces. It will not remove the extra spaces in between words (which the in-built TRIM function in the worksheet does).
For example, suppose you have the following text in cell A1 (with extra spaces in between words):

If I use the above VBA code, it will show a message box as shown below:

Then what’s the alternative?
If you want to remove leading, trailing, as well as double spaces in between words, it’s better to use the following code:
Sub TrimExample1() MsgBox WorksheetFunction.Trim(Range("A1")) End Sub
The above code uses the worksheet TRIM function (instead of using the TRIM function in VBA).
Note: VBA Trim function does not remove the non-whitespace characters – such as newlines (vbNewLine, vbCrLf) etc.
Example 2 – Quickly Remove Leading and Trailing Spaces from a Range of Cells
Suppose you have a data set as shown below where there are leading and trailing spaces:

You can use the below code to instantly clean this data and remove all the leading and trailing spaces:
Sub TrimExample1() Dim Rng As Range Set Rng = Selection For Each Cell In Rng Cell.Value = Trim(Cell) Next Cell End Sub
The above code goes through all the cells in the selection and removes the leading and trailing spaces.

📤You download App EVBA.info installed directly on the latest phone here : https://www.evba.info/p/app-evbainfo-setting-for-your-phone.html?m=1
Leave a Comment