Trim function in macro

[Greg Snidow]

everyone. I saw a good, basic into to macros on the Microsoft site, and one
of the macros I found particularly useful was the one to concatanate two
columns into one. I got it to work nicely, until on some data, the new
concatanated column would have multiple spaces, probably because the data
came from a database field with fixed length. Anyhow, I tried to introduce
the Trim function in the macro, but it does not work. Can anyone help me to
eliminate multiple white spaces between the two values to be concatanated?
Below is the macro as I am trying to use it, and it works fine if you take
out the trims.

Sub Concatanate()

x = 2
Do While Cells(x, 1).Value <> ""
Cells(x, 3).Value = Trim(Cells(x, 1)).Value + " " + Trim(Cells(x,
2)).Value
x = x + 1
Loop

End Sub

 

[Greg Snidow]

Got it to work. Changed...

Trim(Cells(x, 1)).Value to...
Trim(Cells(x,1).Value)

 

[Rick Rothstein \(MVP - VB\)]

One thing you should be aware of, while the worksheet TRIM function and the
VBA Trim function both remove leading and trailing spaces, the worksheet
version also collapses multiple (consecutive) spaces down to a single space
whereas the VBA version does nothing with them. Hence, on a worksheet,
this...

=TRIM(" A B C ")

returns "A B C", whereas within VB code this

MsgBox Trim(" A B C ")

displays "A B C". If you want or need the worksheet version of
TRIM in your VB code, you can call it this way...

MsgBox Application.WorksheetFunction.Trim("A B C")

which will then display "A B C", same as what gets returned on the
worksheet.

Rick