Need help with vba code for deleting trailing spaces before or after an entry in a co

[lucpian]

Hi All,

I do have a vba code that I wrote that validate length of entries in a column, and is working, but does not check if there is a trailing space before or after the entry. Please, I would be grateful if anyone in this forum will help me out.

Thanks

Lucpian

[MikeO]

It sounds like you just need to use the Trim function.
[VBA]NewEntryString = Trim(OldEntryString)[/VBA]

[lucpian]

Here is the code
[VBA]
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' checklength
' This is part of the fields Validation calling function
' which check for length of entries in cells in the Worksheet, and colors it
' yellow.
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Function checklength(columnname As Integer, length As Integer)
Dim rowcount
Dim R
rowcount = Range("A65536").End(xlUp).Row
For R = 2 To rowcount
strVal = Sheet1.Cells(R, columnname).Value
If strVal <> "" And (Not (Len(strVal) = length)) Then
Sheet1.Cells(R, columnname).Interior.ColorIndex = 6
End If
Next

End Function
[/VBA]
My question is where do I place this trim code to indicate that I do not want a trailing space before or after?

Thanks

Lucpian

[mdmackillop]

Hi Lucpian,
When you post code, please select it and click the VBA button to format it as shown.

[MikeO]

Try this:
[VBA]''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' checklength
' This is part of the fields Validation calling function
' which check for length of entries in cells in the Worksheet, and colors it
' yellow.
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Function checklength(columnname As Integer, length As Integer)
Dim rowcount
Dim R
rowcount = Range("A65536").End(xlUp).Row
For R = 2 To rowcount
strVal = Trim(Sheet1.Cells(R, columnname).Value)
If strVal <> "" And (Not (Len(strVal) = length)) Then
Sheet1.Cells(R, columnname).Interior.ColorIndex = 6
End If
Next
End Function
[/VBA]

I'm slightly unclear about what you really want, so this is just a guess.

[Bob Phillips]

[vba]

Function checklength(columnname As Integer, length As Integer)
Dim rowcount
Dim R
rowcount = Range("A65536").End(xlUp).Row
For R = 2 To rowcount
strVal = Sheet1.Cells(R, columnname).Value
If Trim(strVal) <> "" And Len(Trim(strVal)) <> length Then
Sheet1.Cells(R, columnname).Interior.ColorIndex = 6
End If
Next
End Function
[/vba]

[mdmackillop]

Note that Trim will not handle non-printing characters such as Chr(160) which can occur in imported data.