Using TRIM with Evaluate Isn't Working

[Eebigdog]

Hi,

I'm trying to use the following code to remove any leading or trailing spaces from values
in column "C" of my worksheet:

              With Range(Cells(3, "C"), Cells(Rows.Count, "C").End(xlUp)
                   .Value = Evaluate(Trim(" & .Address & "))
              End With

Starting with initial values in column "C" of: J9768A, JC028A#A1X, JC085A, etc.,
after running the above mentioned code, I get "#VALUE!" in all of the cells in column "C".
Additionally, I tried to substitute ".Value" for ".Address" but get the same result.
Any ideas why I'm getting the "#VALUE!" result?

Thank You for any help provided.

[RobDog888]

Thread Moved to Office Development Forum from Visual Basic 6 Forum

[westconn1]

this does not make sense, you are effectively trying to evaluate the address of each cell

Like

              With Range(Cells(3, "C"), Cells(Rows.Count, "C").End(xlUp)
                   .Value = Evaluate(Trim("$c$3"))
              End With

what are you actually trying to achieve?

[Eebigdog]

westconn1,
Basically, trying to use the "Evaluate" function -
that works on a range object - to execute the TRIM
function on all values within the range specified.
I realize this could also be done with a For-Next
loop but I think the Evaluate would run faster and,
I would like to learn how to use it.

[Siddharth Rout]

Here is one piece of code which you will rarely see being used

You can TRIM a range in one line i.e without looping.

    Sub Sample()
        [C1:C1000] = [INDEX(TRIM(C1:C1000),)]
    End Sub