TRIM function - for the whole sheet

[Shetty]

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,

 

[Guest]

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

 

[Alan Howells]

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

 

[Shetty]

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

 

[Tom Ogilvy]

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,xlTextValues)
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

 

[ssexcel]

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

Dim rng as Range, cell as Range

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.

 

[Tom Ogilvy]

Is Name a defined name such as Insert=>Name=>Define or do you mean the
column has the word Name in the first row. This should handle both, but you
could remove what is not appropriate.

Dim rng as Range, cell as Range
Dim rng1 as Range
On Error Resume Next
set rng1 = Thisworkbook.Names("Name").RefersToRange
On Error goto 0
if rng1 is nothing then
for i = 1 to 256
if lcase(cells(1,i).Value) = "name" then
set rng1 = Cells(1,i).EntireColumn.Cells
exit for
End if
Next
End if
if rng1 is nothing then
msgbox "Can't find column"
Exit Sub
End if
On error resume next
set rng = ActiveSheet.Cells.SpecialCells(xlConstants,xlTextValues)
On Error goto 0
if not rng is nothing then
for each cell in rng
cell.Value = strConv(application.Trim(cell.Value),vbProperCase)
Next
Else
msgbox "No text values found"
End if


Proper and StrConv with the vbProperCase argument are not smart enough to
know capitalization rules for Names. The just capitalize the first letter
of each word.

I think you would need some type of dictionary that list all the unique
capitalizations or code the rules.

 

[ssexcel]

Is Name a defined name such as Insert=>Name=>Define or do you mean
the
column has the word Name in the first row. This should handle both,
but you
could remove what is not appropriate.
Proper and StrConv with the vbProperCase argument are not smart
enough to
know capitalization rules for Names. The just capitalize the first
letter
of each word.

I think you would need some type of dictionary that list all the
unique
capitalizations or code the rules.

Thanks for your response. I meant the column with heading Name. I
would come back to you in case I face any difficulty.

Best regards