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

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

Hi there,

Thanks for your response.

I tested it as you recommended and the code is working. It seems to be the
actual cell content that doesn't trim. I tried it with the regular trim
function in Excel with the same result.

The data was imported, so perhaps there's something not apparent to the eye.

Back to the drawing board.

"Gary''s Student" wrote:

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

On Sep 29, 6:31*pm, Joyce wrote:
Hi there,

Thanks for your response.

I tested it as you recommended and the code is working. *It seems to be the
actual cell content that doesn't trim. *I tried it with the regular trim
function in Excel with the same result.

The data was imported, so perhaps there's something not apparent to the eye.

Back to the drawing board.



"Gary''s Student" wrote:
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- Hide quoted text -

- Show quoted text -

Try something like this:
Dim cell As Range
'Also Treat CHR 0160, as a space (CHR 032)
Selection.Replace What:=Chr(160), Replacement:=Chr(32), _
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
'Trim in Excel removes extra internal spaces, VBA does not
On Error Resume Next 'in case no text cells in selection
For Each cell In Intersect(Selection, _
Selection.SpecialCells(xlConstants, xlTextValues))
cell.Value = Application.trim(cell.Value)
Next cell

Thanks Armando,

I will give that a try later today when I have a chance to get back to the
problem.



"Armando" wrote:

On Sep 29, 6:31 pm, Joyce wrote:
Hi there,

Thanks for your response.

I tested it as you recommended and the code is working. It seems to be the
actual cell content that doesn't trim. I tried it with the regular trim
function in Excel with the same result.

The data was imported, so perhaps there's something not apparent to the eye.

Back to the drawing board.



"Gary''s Student" wrote:
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- Hide quoted text -

- Show quoted text -

Try something like this:
Dim cell As Range
'Also Treat CHR 0160, as a space (CHR 032)
Selection.Replace What:=Chr(160), Replacement:=Chr(32), _
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
'Trim in Excel removes extra internal spaces, VBA does not
On Error Resume Next 'in case no text cells in selection
For Each cell In Intersect(Selection, _
Selection.SpecialCells(xlConstants, xlTextValues))
cell.Value = Application.trim(cell.Value)
Next cell