Extra space with vbNewLine

[brownti via OfficeKB.com]

Below is a section of my code that works for the purpose i would like except
for one thing. I get an extra space at the end of each myCell.Value. It
seems to be everytime i use a vbNewLine it adds an extra space and then goes
to the next line. Is there a different funtion i could use?


myMsg = vbNewLine & vbNewLine & "Labor Rate: " & Range("baselaborrate") &
vbNewLine & vbNewLine
For Each myCell In myRng.Cells
If myCell.EntireRow.Hidden = True Then
'do nothing
Else
myMsg = myMsg & myCell.Offset(0, -2).Value & " Millwork: " & myCell.
Value & vbNewLine
End If
Next myCell
myMsg = myMsg & vbNewLine

 

[Tom Ogilvy]

you can use (all work about the same in a msgbox)

chr(10)
chr(13)
vbCr
vbLf
vbCrLf
vbNewline

Sure the space isn't included in the text of the cell.

 

[brownti via OfficeKB.com]

I guess maybe i should have mentioned that myMsg goes to the .body of an
email. Still get the spaces with the below suggestions...any other thoughts?
The space isnt in the cell...

you can use (all work about the same in a msgbox)

chr(10)
chr(13)
vbCr
vbLf
vbCrLf
vbNewline

Sure the space isn't included in the text of the cell.

Below is a section of my code that works for the purpose i would like except
for one thing. I get an extra space at the end of each myCell.Value. It

[quoted text clipped - 12 lines]

Next myCell
myMsg = myMsg & vbNewLine

 

[Rick Rothstein \(MVP - VB\)]

I guess maybe i should have mentioned that myMsg goes to the .body of an

email. Still get the spaces with the below suggestions...any other
thoughts?
The space isnt in the cell...

I think the spaces might be coming from the email program (I notice them
when I copy/paste from email messages). What if you apply the RTrim function
to the email lines that you end up concatenating vbNewLine onto. I think
like this may do for you....

myMsg = myMsg & myCell.Offset(0, -2).Value & _
" Millwork: " & RTrim(myCell.Value) & vbNewLine

and maybe here too...

myMsg = RTrim(myMsg) & vbNewLine

Rick