Frank wrote :
the original code was

'remove before and after blanks via TRIM function
Range("D2", Range("C2").End(xlDown).Offset(0, 1)).FormulaR1C1 =
"=TRIM(RC[-1])"
Columns("C:C").Value = Columns("D:D").Value

I ran the code without the Columns("C:C").Value = Columns("D:D").Value
and it worked fine
but when included, if fails at the 16th sheet.

I know I could do cell.value = trim(cell).value but I
find .FormulaR1C1 to work faster.

Well, having more info about what you're trying to do certainly helps.
In this case I suggest to just trim the values in "C:C" rather than
enter a formula in "D:D" to do so and then copy the result back to
"C:C". Doesn't make sense to go to all the trouble when you could use
the VBA Trim() function on the "C:C" cells.

Here's an example:

Sub TrimLeftRightSpaces()
Dim c As Range, lLastRow As Long
lLastRow = ActiveSheet.UsedRange.Rows.Count
For Each c In Range(Cells(1, "C"), Cells(lLastRow, "C"))
If Not c = "" Then c.Value = Trim$(c.Value)
Next
End Sub

HTH
Garry

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc