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.
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)
🔄 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 three ways to open the Microsoft Visual Basic window.
🔼 Using Keyboard Shortcuts
Press ALT+F11 altogether to open the Microsoft Visual Basic window.
🔼 Using Developer Tab
In an Excel worksheet, Go to Developer Tab > Select Visual Basic. The Microsoft Visual Basic window appears.
🔼 Using Worksheet Tab
Go to any worksheet, Right-Click on it > Choose View Code (from the 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.
1. Removing Trailing Spaces from a Text String Using VBA RTrim Function
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.
➤ 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 
➤ 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.
Read More: How to Use VBA Space Function in Excel (3 Examples)
2. Removing Trailing Spaces from Cell Reference Using VBA RTrim Formula
VBA RTrim function can fetch string from the provided cell reference and then remove trailing spaces if there are 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,
➤ Write the following code in the Microsoft Visual Basic window.
Sub Using_Formula_CellReference()
Range("C3").Value = RTrim(Range("B3"))
End Sub
➤ 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.
➤ If you want to cross-check the outcome, just click on the cell (i.e., C3). Afterward, you see the cursor stick to the tail-end of the string indicating no trailing spaces as you see in the below screenshot.
Read More: How to Use TRIM Function in VBA in Excel (Definition + VBA Code)
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 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
➤ 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.
➤ You can compare the VBA RTrim function’s resultant values to the Worksheet TRIM function’s outcomes depicted in the below picture.
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:
- How to Use VBA Case Statement (13 Examples)
- How to Use IsDate Function in VBA (3 Examples)
- VBA If – Then – Else Statement in Excel (4 Examples)
- How to Use VBA Mod Operator (9 Examples)
- How to Use the VBA Environ Function (4 Examples)
4. Removing Multiple Trailing Spaces from Multiple Cell References
We convert multiple strings into trailing spaceless strings and 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 and 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).
➤ 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
➤ 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.
Related Content: How to Use VBA StrComp in Excel (5 Common Examples)
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 and then display them in respective cells. In this example, we use a loop and the OFFSET function to make the string appear in its 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.
➤ 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
➤ 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.
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.
Download Practice Workbook
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.
