sham

Try this macro.

Sub TRIM_EXTRA_SPACES()
Dim cell As Range
For Each cell In Selection
If (Not IsEmpty(cell)) And _
Not IsNumeric(cell.Value) And _
InStr(cell.Formula, "=") = 0 _
Then cell.Value = Application.Trim(cell.Value)
Next
End Sub

First...create a backup copy of your original workbook.

To create a General Module, hit ALT + F11 to open the Visual Basic Editor.

Hit CRTL + r to open Project Explorer.

Find your workbook/project and select it.

Right-click and InsertModule. Paste the three sets of code in there. Save the
workbook and hit ALT + Q to return to your workbook.

Run the ChangeWidthAndHeight macro by going to ToolMacroMacros.

You can also assign this macro to a button or a shortcut key combo.

NOTE: the code operates on whatever sheet is selected before running the macro.


Gord Dibben MS Excel MVP

On Sat, 16 Jun 2007 07:42:00 -0700, sham wrote:

i am not able to replace the space in cells
ex: i have a data range of a1 to d90
in that all the cell have the spaces at the end
now i wanted to remove all those at a time