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,

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,
.

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,
.

Thanks Alan and Peter.
I will try out right now and let you know the results. But
I think this is exectly what I want and it must 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,
.



.

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,
.



.

Tom Ogilvy wrote:
You can narrow down the magnitude of your work by only working on
string
constants

Dim rng as Range, cell as Range
--
Regards,
Tom Ogilvy


Tom,

Thanks for your wonderful explanations and your various posts helping
people like us.

I need to ask you help on these 2 things:

1. If I want to apply the code only to a particular column and not the
whole sheet (not ALL) how do I do it ? I want to apply worksheet
function proper to a column named NAME.

2. Second question when I use Proper function in Excel (97?) it does
not change these names properly. How do I achieve this

joan vannocker-sampson should be Joan VanNocker-Sampson john d mckeon
should be John D McKeon
arthur l dewyse should be Arthur L DeWyse

Thanks in advance.



------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~ View and post usenet messages directly from http://www.ExcelForum.com/