Your posted code works (removes leading and following and extra internal
spaces).

Make sure Selection is good. Just before the For loop put:

MsgBox(Selection.Address)
--
Gary''s Student - gsnu200905


"Joyce" wrote:

I have a sub that filters, selects cells and pastes them (transposed) as
headers in another area of the worksheet. I'd like to then trim the extra
spaces from the newly pasted headings. I'm very familiar with the Trim
function in Excel, but would like to to it in my sub. I've tried adding the
following code to my sub but, while it doesn't generate any error messages,
it just doesn't work:

-----Beginning of Code......
With Selection.(Formatting, etc.)
End With
Selection.SpecialCells(xlCellTypeConstants, 23).Select
------Then I add the following:

Dim cell

' Find all the cells in the current selection.
For Each cell In Selection
'This code repeats once for each cell in the selection.
cell.Value = Application.WorksheetFunction.Trim(cell.Value)
Next

End Sub