Greetings!

I'm using Excel 2007 and would like to apply the TRIM funtion to the entire
spreadsheet. I know how to do it for a given cell and then copy it to the
rest of the column but would like to do a whole spreadsheet at the same time
if possible.

Thanks!

Sub TrimData()

For each cell In Activesheet.usedrange

cell.Value = Trim(cell.Value)
Next cell
End Sub

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)



"Chimelle" wrote in message
...
Greetings!

I'm using Excel 2007 and would like to apply the TRIM funtion to the
entire
spreadsheet. I know how to do it for a given cell and then copy it to the
rest of the column but would like to do a whole spreadsheet at the same
time
if possible.

Thanks!

1) On Sheet2 in A1, enter =TRIM(Sheet1!A1)
2) Copy this as far to the right and down as needed to capture all used
cells on Sheet1
3) Select all of Sheet2 used cells (CTRL+A or click the box where column
and row headers meet (to the left of A; above 1)
4) Use Copy
5) With the cells still selected use Edit | Paste Special with Values
checked
6) Now Sheet2 has values and Sheet1 could be deleted - but do double check
first.
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"Chimelle" wrote in message
...
Greetings!

I'm using Excel 2007 and would like to apply the TRIM funtion to the
entire
spreadsheet. I know how to do it for a given cell and then copy it to the
rest of the column but would like to do a whole spreadsheet at the same
time
if possible.

Thanks!

That might not do exactly what the OP asked for. The VBA Trim function
**only** removes spaces from the left and right sides of the text... the
worksheet TRIM function collapses multiple internal spaces down to single
spaces as well. You could modify your code like this (if you want to remain
wholly within VBA) to match this functionality....

Sub TrimData()
Dim CellText As String
Dim Cell As Range
For each Cell In Activesheet.UsedRange
CellText = Cell.Value
CellText = Trim(CellText)
Do While InStr(CellText, " ")
CellText = Replace(CellText, " ", " ")
Loop
Next
Cell.Value = CellText
End Sub

or, if you don't mind reaching out to the worksheet world, like this...

Sub TrimData()
Dim Cell As Range
For each Cell In Activesheet.UsedRange
Cell.Value = Application.WorksheetFunction.Trim(Cell.Value)
Next
End Sub

Rick



"Bob Phillips" wrote in message
...
Sub TrimData()

For each cell In Activesheet.usedrange

cell.Value = Trim(cell.Value)
Next cell
End Sub

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my
addy)



"Chimelle" wrote in message
...
Greetings!

I'm using Excel 2007 and would like to apply the TRIM funtion to the
entire
spreadsheet. I know how to do it for a given cell and then copy it to
the
rest of the column but would like to do a whole spreadsheet at the same
time
if possible.

Thanks!

but then it again it might.

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)



"Rick Rothstein (MVP - VB)" wrote in
message ...
That might not do exactly what the OP asked for. The VBA Trim function
**only** removes spaces from the left and right sides of the text... the
worksheet TRIM function collapses multiple internal spaces down to single
spaces as well. You could modify your code like this (if you want to
remain wholly within VBA) to match this functionality....

Sub TrimData()
Dim CellText As String
Dim Cell As Range
For each Cell In Activesheet.UsedRange
CellText = Cell.Value
CellText = Trim(CellText)
Do While InStr(CellText, " ")
CellText = Replace(CellText, " ", " ")
Loop
Next
Cell.Value = CellText
End Sub

or, if you don't mind reaching out to the worksheet world, like this...

Sub TrimData()
Dim Cell As Range
For each Cell In Activesheet.UsedRange
Cell.Value = Application.WorksheetFunction.Trim(Cell.Value)
Next
End Sub

Rick



"Bob Phillips" wrote in message
...
Sub TrimData()

For each cell In Activesheet.usedrange

cell.Value = Trim(cell.Value)
Next cell
End Sub

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my
addy)



"Chimelle" wrote in message
...
Greetings!

I'm using Excel 2007 and would like to apply the TRIM funtion to the
entire
spreadsheet. I know how to do it for a given cell and then copy it to
the
rest of the column but would like to do a whole spreadsheet at the same
time
if possible.

Thanks!

True (which I implied by saying it might not)... all I was trying to do was
alert the OP to the possibility that it might not do what was anticipated of
it if the data contained multiple internal spaces (and to offer a work
around if one was needed).

Rick


"Bob Phillips" wrote in message
...
but then it again it might.

"Rick Rothstein (MVP - VB)" wrote in
message ...
That might not do exactly what the OP asked for. The VBA Trim function
**only** removes spaces from the left and right sides of the text... the
worksheet TRIM function collapses multiple internal spaces down to single
spaces as well. You could modify your code like this (if you want to
remain wholly within VBA) to match this functionality....

Sub TrimData()
Dim CellText As String
Dim Cell As Range
For each Cell In Activesheet.UsedRange
CellText = Cell.Value
CellText = Trim(CellText)
Do While InStr(CellText, " ")
CellText = Replace(CellText, " ", " ")
Loop
Next
Cell.Value = CellText
End Sub

or, if you don't mind reaching out to the worksheet world, like this...

Sub TrimData()
Dim Cell As Range
For each Cell In Activesheet.UsedRange
Cell.Value = Application.WorksheetFunction.Trim(Cell.Value)
Next
End Sub

Rick



"Bob Phillips" wrote in message
...
Sub TrimData()

For each cell In Activesheet.usedrange

cell.Value = Trim(cell.Value)
Next cell
End Sub

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my
addy)



"Chimelle" wrote in message
...
Greetings!

I'm using Excel 2007 and would like to apply the TRIM funtion to the
entire
spreadsheet. I know how to do it for a given cell and then copy it to
the
rest of the column but would like to do a whole spreadsheet at the same
time
if possible.

Thanks!