How to Use VBA RTrim Function (5 Suitable Examples)

The VBA RTrim function is a built-in String or Text function in Excel. The RTrim function is only applicable in VBA macros. We can’t use the RTrim function in Excel Worksheets. Instead, Excel has the TRIM function to remove all kinds of spaces (i.e., Leading, Trailing, and In-between). We also display a comparison between the VBA RTrim and Worksheet TRIM functions. Overall, in this article, we discuss and demonstrate the VBA RTrim function and its usage.

Overview of VBA RTrim function


Download Excel Workbook


Excel VBA RTrim Function: Syntax and Arguments

🔄 Function Objective:

To remove the trailing spaces from any string expression.

🔄 Syntax:

The syntax of the VBA RTrim function is

RTrim(string)

RTrim function syntax

🔄 Arguments Explanation:

Argument Required/Optional Explanation
string Required any valid string expression

🔄 Return Parameter:

 The VBA RTrim function returns a string without any trailing spaces.

🔄 Applies To:

 Microsoft Excel Version 2003, Excel 2011 for Mac and Onwards.


⧭ Opening Microsoft Visual Basic and Inserting Code in Sheet Window

Before proceeding to demonstrate any examples, it’s necessary to know the ways to open and insert a Sheet Code window in Microsoft Visual Basic.

🔄 Opening Microsoft Visual Basic: There are mainly three ways to open Microsoft Visual Basic window.

🔼 Using Keyboard Shortcuts

 Press ALT+F11 altogether to open Microsoft Visual Basic window.

🔼 Using Developer Tab

In an Excel worksheet, Go to Developer Tab > Select Visual Basic. The Microsoft Visual Basic window appears.

developer option-VBA RTrim

🔼 Using Worksheet Tab

Go to any worksheet, Right-Click on it > Choose View Code (from the Context Menu).

Context menu


🔄 Inserting Macro Code in Sheet Window

🔼 After opening the Microsoft Visual Basic window, Select a Worksheet > Double-Click on it. A window appears where you can write or insert macro code for the respected Worksheet.

sheet window-VBA RTrim


5 Suitable Examples to Use Excel VBA RTrim Function

Example 1: VBA RTrim Removes Trailing Spaces from a Text String

The RTrim function is only available in Excel VBA. Among three types of spaces (i.e., Leading, Trailing, and In-between), the RTrim function removes only Trailing spaces from a string. Whereas its counterpart Worksheet TRIM function removes all three space types.

For this example, we only use a string (i.e.,”  trailing spaces in text  “) that has both leading and trailing spaces.

Before applying the VBA RTrim function, we use the Worksheet TRIM function to remove all kinds of spaces from the given string. As shown in the screenshot the TRIM function removes not only the trailing spaces but the leading spaces also.

removing trailing spaces

Paste the following macro code in a Worksheet Code of Microsoft Visual Basic window,

Sub Removing_Trailing_Spaces()
MsgBox ("After RTrim:" & RTrim("  trailing spaces in text  "))
End Sub

Macro code

in the code,

1 – start the macro procedure by declaring the Sub name. You can assign any name to the code.

2 – display the string without any trailing spaces in a message box.

Press F5 to run the Macro and it brings out a message box depicting the function’s outcome. And you can compare the result with the TRIM function as it removes all types of spaces but RTrim doesn’t.

removing trailing spaces result-VBA RTrim

Read More: How to Use VBA Space Function in Excel (3 Examples)


Example 2: Removing Trailing Spaces from Cell Reference Using VBA RTrim Formula

VBA RTrim function can fetch string from provided cell reference and then removes trailing spaces if there’s any in the string. In this example, we have a text string in cell B3, and we want the trailed spaceless string in cell C3. To achieve this, execute the following sequences,

cell reference

Write the following code in Microsoft Visual Basic window.

Sub Using_Formula_CellReference()
Range("C3").Value = RTrim(Range("B3"))
End Sub

macro code-cell reference

From the above image, in the sections,

1 – begin the macro code declaring the VBA Macro Code’s Sub name.

2 – assign the =RTrim() formula to display the resultant string in cell C3.

Hit F5 to run the macro and it displays the resultant string in destined cell C3 as depicted in the following image.

result-VBA RTrim

If you want to cross-check the outcome, just click on the cell (i.e., C3). Afterward, you see the cursor sticks to the tail-end of the string indicating no trailing spaces as you see in the below screenshot.

cross-check-cell reference

Read More: How to Use TRIM Function in VBA in Excel (Definition + VBA Code)


Example 3: Removing Trailing Spaces from Multiple Strings

In previous examples, we demonstrate the VBA RTrim function to only remove the trailing spaces from a single string. We can remove trailing spaces from multiple strings. In this example, we remove trailing spaces from multiple strings and display them in the immediate window. Follow the below sequences to do so.

Use the below code in any Microsoft Visual Basic’s Sheet code window.

Option Explicit
Sub Multiple_Strings()
Dim Value1 As String
Dim Value2 As String
Dim Value3 As String
Value1 = RTrim("  trailing spaces text   ")
Value2 = RTrim("Excel is awesome    ")
Value3 = RTrim("it's  a sunny  day   ")
Debug.Print Value1
Debug.Print Value2
Debug.Print Value3
End Sub

macro code-multiple string

The code’s sections,

1 – initiate the macro procedure by declaring the Sub name.

2 – declare the variables Value1, Value2, and Value 3 as String. As we mentioned earlier, the return values will be in text or string, we declare the variables as String.

3 – assign the values to variables. Where Value1 = RTrim(”  trailing spaces text   “), Value2 = RTrim(“Excel is awesome    “), and Value3 = RTrim(“it’s  a sunny  day   “). You can assign any text or string to the variables.

4 – print the resultant string in the Immediate window below the sheet code window.

Use F5 to run the macro and it brings the trailing spaceless strings out in the immediate window as shown in the following image.

multiple string-VBA RTrim

You can compare the VBA RTrim function’s resultant values to the Worksheet TRIM function’s outcomes depicted in the below picture.

TRIM vs RTrim

From the above screenshot, You can see where it’s the Worksheet TRIM or VBA RTrim function, trailing spaces in strings are removed. However, only the TRIM function removes all types of spaces from any given string.

Read More: How to Use VBA LTrim Function in Excel (4 Examples)


Similar Readings:


Example 4: Removing Multiple Trailing Spaces from Multiple Cell References

We convert multiple strings into trailing spaceless strings then display them in the immediate window in Method 3. Now, we can take the example a step further by fetching multiple strings using cell references then displaying them in destined cells. In the below image, we have strings in certain cells (i.e., B3, B4, and B5), we want them to appear trailing spaceless in certain cells (i.e., C3, C4, and C5).

multiple cell reference

Type the following code in a Sheet Code’s window.  

Option Explicit
Sub RTrim_Multiple_Cell_Reference()
Range("C3").Value = RTrim(Range("B3"))
Range("C4").Value = RTrim(Range("B4"))
Range("C5").Value = RTrim(Range("B5"))
End Sub

macro code-multiple cell reference 

From the above image, the code’s sections,

1 – take forward the macro by setting the Sub name.

2 – assign the strings to the formula (i.e., =RTrim()) with cell reference (i.e.,B3, B4, or B5) as an argument. And display the resultant strings in certain cells (i.e., C3, C4, or C5). You can use any cell as a reference.

Press F5 to run the macro and it makes the strings appear in referenced cells as shown in the following picture.

multiple cell reference-VBA RTrim

Related Content: How to Use VBA StrComp in Excel (5 Common Examples)


Example 5: Removing Trailing Spaces from a Range

A range of cells can also be used to refer to strings after that we convert the referenced strings into trailing spaceless strings using the Rtrim function then display them in respective cells. In this example, we use a loop and the OFFSET function to make the string appear in their destined cells.

We have strings in cells, and we want the trailing spaceless strings in certain cells (i.e., C3, C4, and C5). Also, we want the resultant strings executed by the Worksheet TRIM function to appear in adjacent cells.

range-VBA RTrim

Paste the following code in a Sheet’s Code window.

Sub Removing_Trailing_Spaces_Range()
Dim i As Range
For Each i In Range("B3:B5")
i.Offset(0, 1) = RTrim(i)
Next i
End Sub

macro code-range

The code is marked in parts,

1 – begin the macro by setting the Sub name.

2 – declaring the variable as Range.

3 – assign range value (i.e., B3:B4) for each iteration.

4 – create a loop and display the string in cell position at the end of each iteration.

Push the F5 key to run the Macro and it displays the resultant strings in destined cells.

range-VBA RTrim

Related Content: How to Use VBA Str Function in Excel (4 Examples)


⧭ Things to Keep in Mind

🔄 The VBA RTrim function only removes the trailing spaces while the Worksheet TRIM function removes all types of spaces from a string.

🔄 If the given or referenced string is NULL, the VBA RTrim function returns NULL.


Conclusion

In this article, we discuss and demonstrate the uses of the VBA RTrim function. We try to clear the basics and cover typical uses of this function. I hope the above-described uses of the VBA RTrim function intrigue you to use the function more efficiently. If you have further queries or feedback, please let me know in the comment section. See you in my next article.


Related Articles

Maruf Islam

Maruf Islam

My self, Maruf Islam, an engineer and Excel & VBA Content developer on Exceldemy. I enjoy solving problems, finding workable solutions, and most of the part I really like to take on challenges. On Exceldemy I write articles discussing various way outs of Microsoft Excel's stuck ons.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo