Solved: HELP with TRIM !!

[mitko007]

I have a column that contains text values in the cells.
Im trying to delete the spaces between the words using trim but it somehows does not work in my macro.

my code goes basicly like this:

for i=n to m

Worksheets("xxxx").Cells(i, 7)=Trim(Worksheets("xxxx").Cells(i, 7))

End for
next i

but it doesnt work



Thanks for the help

[gnod]

delete the "End For"
and specify a value for "n" and "m"

trim doesn't delete spaces between words. it will only delete the leading and trailing spaces..

[Norie]

Trim in VBA is different from the TRIM worksheet function.

It only deletes leading and trailing spaces.

The TRIM worksheet function does that, also it replaces any multiple spaces between words with a single space.

Is that what you are trying to do?

If so you can access the worksheet function via Application.WorksheetFunction.

[Cyberdude]

Here's something I wrote for myself:
Function IntTrim(InputString As String, _
Optional MaxBlanks As Long = 1, _
Optional RemvLeadTrail As Boolean = True, _
Optional Test As Boolean = False) As String '9/16/05

'Removes all but "MaxBlanks" separator blanks (if any) internal to a string.

'If "RemvLdgTrlg" = TRUE (default), then also removes leading and/or trailing blanks
'If "Test" = TRUE, then displays via Msgbox the returned string with periods
' substituted for each blank in the string for ease of viewing.
'Argument "MaxBlanks". Optional (defaults to 1) Value must be 0 or any positive integer.
' If there are more than "MaxBlanks" blanks separating two characters in the input string,
' they will be reduced to "MaxBlanks" blanks.
' Note that specifying 0 will cause all internal blanks to be removed.
'Argument "RemvLdgTrlg". Optional (defaults to TRUE)
' If specified as FALSE, then the leading and trailing blanks will not be removed.
'Argument "Test". Optional (defaults to FALSE)
' If specified as TRUE, then the input and output strings will be displayed (via Msgbox)
' with a period substituted for each blank for ease of viewing.
'Examples:
'1. MyString = IntTrim(MyString) This is the usual application. All leading and
' trailing blanks are removed. All blanks (if any) separating two characters will
' be reduced to one blank.
'2. MyString = IntTrim(MyString, 2) This is the usual application, except that the
' minimum number of blanks separating any two characters will be reduced to two.
' If the original string had a single blank separating two characters, it is not be changed.
'3. MyString = IntTrim(MyString, , FALSE) This is the usual application, except that
' the leading and trailing blanks will NOT be removed.
'4. MyString = IntTrim(MyString, , TRUE) This is the usual application, except that the
' output string will be displayed to the user for testing purposes.
Dim N As Long, LenInputString As Long, LenIntTrim As Long
Dim LeadingBlankCnt As Long, TestCnt As Long
Dim Msg As String, DisplayVal As String
Const Title$ = "Personal.xls (IntTrim)"
Application.Volatile (False)

LenInputString = Len(InputString)
On Error GoTo ErrorReturn
'Replace Chr(160) type blanks (if any) with standard blanks
IntTrim = Replace(InputString, Chr(160), Chr(32))
IntTrim = Trim(IntTrim)
LenIntTrim = Len(IntTrim)
Do Until InStr(1, IntTrim, Space(MaxBlanks + 1)) = 0
IntTrim = Replace(IntTrim, Space(MaxBlanks + 1), Space(MaxBlanks))
Loop
'Does caller want leading and trailing blanks removed?
If Not RemvLeadTrail _
Then
LeadingBlankCnt = InStr(1, InputString, Left(Replace(InputString, " ", ""), 1)) - 1
IntTrim = Space(LeadingBlankCnt) & IntTrim & Space(Len(InputString) - LeadingBlankCnt - LenIntTrim)
End If
'Is this executing in "test" mode?
If Test _
Then
DisplayVal = IntTrim
For N = 1 To Len(IntTrim)
InputString = Replace(InputString, " ", ".")
InputString = Replace(InputString, Chr(160), ".")
DisplayVal = Replace(DisplayVal, " ", ".")
DisplayVal = Replace(DisplayVal, Chr(160), ".")
Next N
Msg = "Input string: '" & InputString & "'" & vbCr & _
"Output string: '" & DisplayVal & "'" & vbCr & vbCr & _
"Options" & vbCr & _
" MaxBlanks (internal): " & MaxBlanks & vbCr & _
" RemvLeadTrail: " & RemvLeadTrail & vbCr & _
" Test (mode): True"
MsgBox Msg, , Title
End If
GoTo Finish
ErrorReturn:
IntTrim = CInt(CVErr(xlErrValue))
Finish:
On Error GoTo 0
End Function 'IntTrim'
Hope it helps!

[mitko007]

Trim in VBA is different from the TRIM worksheet function.

It only deletes leading and trailing spaces.

The TRIM worksheet function does that, also it replaces any multiple spaces between words with a single space.

Is that what you are trying to do?

If so you can access the worksheet function via Application.WorksheetFunction.

Thanks for the help...it worked when i used the Application.WorksheetFunction.