How can I remove the trailing space from values with a font color without losing the font color? (I'm not familiar with VBA).
Hi Gary,
Assuming your data to start in A1:
(1) in another column enter the formula
= Trim(A1)
and drag the formula down as far as necessary.
(2) copy the new formula cells
(3) select A1 | Paste Special | Values
'----------------
For a VBA solution, with the workbook which contains the data to be
trimmed selected, copy the following code
'=============>>
Sub TestIt()
Dim WB As Workbook
Dim SH As Worksheet
Dim Rng As Range
Dim rCell As Range
On Error Resume Next
Set Rng = Application.InputBox _
(Prompt:="Select range to be trimmed", _
Type:=8)
If Not Rng Is Nothing Then
For Each rCell In Rng.Cells
With rCell
.Value = RTrim(.Value)
End With
Next rCell
Else
MsgBox "No cells were selected!"
End If
End Sub
'<<=============
Alt-F11 to open the Visual Basic Editor
Insert | Module Edit | Paste
Alt-Q to close the Visual Basic Editor
To run the macro:
Alt-F8 to access the macro list | select "TestIt" | Run
Remember to save your workbook before closing it!
===
Regards,
Norman
