VBA to trim spaces also deleting formulas

[inactiveUser333473]

Hi,

I'm using the following VBA. I want to delete any superfluous spaces without deleting my vlookup formulas but when I run this macro, it deletes everything except cell value.

How do I retain formulas in cells?

VBA:

Sub SelectRange_Trim()
'Trim spaces
Dim MyCell As Range
On Error Resume Next
Selection.Cells.SpecialCells(xlCellTypeConstants, 100).Select
For Each MyCell In Selection.Cells
MyCell.Value = RTrim(MyCell.Value)
MyCell.Value = LTrim(MyCell.Value)
Next
On Error GoTo 0
End Sub

 

[Joe4]

So, are you saying that you have VLOOKUP formulas that are returning values that have extra spaces in them, and you want to remove the spaces from the results it returns? If so, have you just tried using the TRIM function in conjunction with the VLOOKUP?

=TRIM(VLOOKUP(...))