I recently helped a forum user on here and ended up trimming leading and trailing spaces with tedious code like what I posted below but I just know there is a better way.
I was using the Find Method with arguments: RNG_AllNames_dest.Find(ValueToFind, After:=WS_dest.Range("A" & LRW_src), LookAt:=xlWhole, MatchCase:=False), with LRW_src as the last row in the range I'm searching.
In order to avoid something like the code below, do I need to use a Regular Expression or is there an argument in the Find method that will ignore trailing and leading spaces in a string I'm searching?
'/Condition data to have no trailing or leading spaces in source and destination sheets
'/Trim trailing and leading spaces of distination sheet (note RNG_AllNames_dest is used for source sheet here but changed to dest. later)
Set RNG_AllNames_dest = WS_src.Range("A2", "A" & LRW_src)
'/Trim Leading Spaces
For Each c In RNG_AllNames_dest.Cells
AreThereLeadingOrTrailingSpaces = True
Do While AreThereLeadingOrTrailingSpaces
If InStr(c.Value, " ") = 1 Then
c.Value = Right(c.Value, Len(c.Value) - 1)
Else
AreThereLeadingOrTrailingSpaces = False
End If
Loop
Next c
'/Trim Trailing Spaces
For Each c In RNG_AllNames_dest.Cells
AreThereLeadingOrTrailingSpaces = True
Do While AreThereLeadingOrTrailingSpaces
If InStr(Len(c.Value), c.Value, " ") = Len(c.Value) Then
c.Value = Left(c.Value, Len(c.Value) - 1)
Else
AreThereLeadingOrTrailingSpaces = False
End If
Loop
Next c
'/Trim trailing and leading spaces of distination sheet
Set RNG_AllNames_dest = WS_dest.Range("A2", "A" & LRW_dest)
'/Trim Leading Spaces
For Each c In RNG_AllNames_dest.Cells
AreThereLeadingOrTrailingSpaces = True
Do While AreThereLeadingOrTrailingSpaces
If InStr(c.Value, " ") = 1 Then
c.Value = Right(c.Value, Len(c.Value) - 1)
Else
AreThereLeadingOrTrailingSpaces = False
End If
Loop
Next c
'/Trim Trailing Spaces
For Each c In RNG_AllNames_dest.Cells
AreThereLeadingOrTrailingSpaces = True
Do While AreThereLeadingOrTrailingSpaces
If InStr(Len(c.Value), c.Value, " ") = Len(c.Value) Then
c.Value = Left(c.Value, Len(c.Value) - 1)
Else
AreThereLeadingOrTrailingSpaces = False
End If
Loop
Next c
Bookmarks