Excel VBA Length-1 In a Range

I recently got into Excel macro development after a long time of not having the need to.

I have one column with two-hundred rows where each row has a value. I wrote a loop to iterate to each row value, read the current value and then write the value back minus the last character.

Here is some actual (and pseudo) code of what I wrote.

Dim theRow as Long
Dim totRow as Long
Dim fooStr as String


theRow = 2 'we begin on the second row of the colummn
totRow = 201 'there are 200 values


For theRow = 2 to totRow
    fooStr = WorkSheets(DestSheet).Cells(theRow,"A").Formula 'read the cell value
    fooStr = Left(fooStr,Len(fooStr)-1 'subtract the last character from the value
    Cells(theRow,1).Value = fooStr 'write the开发者_高级运维 value back
Next theRow

After I did some reading I learned that it is best practice to read and write values using a Range. Is it possible to rewrite what I am doing using a Range so it willl go faster.

Here is what I came up with so far.

Range("A2:A201").Value = Len(Range.Left("A2:A201").Value)-1

However, this doesn't work.

Any clues on how to do this if this is indeed possible?

Thanks for any tips.

If you want maximum performance (you don't need it for 200 rows, but...) you have to minimize the number of reads and writes (mostly writes) to ranges. That means reading the whole range into an array, manipulating the array, then writing it back to the range. That's one read and one write compared to 200 in a loop. Here's an example.

Sub RemoveLastChar()

    Dim vaValues As Variant
    Dim i As Long

    vaValues = Sheet1.Range("A2").Resize(200).Value

    For i = LBound(vaValues, 1) To UBound(vaValues, 1)
        vaValues(i, 1) = Left$(vaValues(i, 1), Len(vaValues(i, 1)) - 1)
    Next i

    Sheet1.Range("A2").Resize(UBound(vaValues, 1), UBound(vaValues, 2)).Value = vaValues

End Sub

You could do something like

 Sub StringTrim()
    Dim xCell as Range 
    Range("A1:A201").Select

    For Each xCell in Selection
       xCell.Value = Left(xCell.Value, Len(xCell.Value) - 1)
    Next

 End Sub

I don't know what kind of speed improvements you are seeking, but that would also do the job.

You might know this already but putting Application.ScreenUpdating = False at the top of your code can speed it up significantly (unless you like to watch everything flash by as the script works). You should reset the value to True at the end of your code.