You can narrow down the magnitude of your work by only working on string
constants

Dim rng as Range, cell as Range
On error resume next
set rng = ActiveSheet.Cells.SpecialCells(xlConstants,xlTextV alues)
On Error goto 0
if not rng is nothing then
for each cell in rng
cell.Value = application.Trim(cell.Value)
Next
Else
msgbox "No text values found"
End if


--
Regards,
Tom Ogilvy


"Shetty" wrote in message
...
Thanks Alan and Peter.
I will try out right now and let you know the results. But
I think this is exectly what I wantAmust work.

Thanks again.
Shetty

-----Original Message-----
You can use the UsedRange property of the Worksheet
object. This will return
the maximum range (in one area) that is used. So you
won't have to go
through the entire worksheet (most of it is not actually
used).

Set rng = ActiveWorksheet.UsedRange

Alan

wrote in message
...
It would take a long time to complete. you might be
better
selecting the range and working with that.

Sub TrimSelection()

For Each c In Selection
c.Value = Trim(c)
Next c

End Sub

Sub TrimRange()
Dim rng As Range
Set rng = Range("A1:z22") 'change this to suit
For Each c In rng
c.Value = Trim(c)
Next c
End Sub

Regards
Peter

-----Original Message-----
Request your guidance on how can I execute the trim
function for all the cells in a sheet. Is there any VB
code or macro which can do this?

Regards,
.



.