Better way to apply change to column of values

[Tod]

I have a column with dates and times. I want to take the
date out. So I'm doing it like this:

For each Cell in ActiveSheet.Range("A2:A10000")
Cell.Value = Cell.Value - Int(Cell.Value)
Next Cell

This works, but it adds minutes to the procedure. Is
there a statement or other that will just make the change
to the entire range at once?

tod

 

[JE McGimpsey]

one way:

Dim vArr As Variant
Dim i As Long
With ActiveSheet.Range("A2:A10000")
vArr = .Value
For i = 1 To UBound(vArr)
vArr(i, 1) = Int(vArr(i, 1))
Next i
.Value = vArr
End With

 

[Tod]

Wow! Big difference. Thanx.

-----Original Message-----
one way:

Dim vArr As Variant
Dim i As Long
With ActiveSheet.Range("A2:A10000")
vArr = .Value
For i = 1 To UBound(vArr)
vArr(i, 1) = Int(vArr(i, 1))
Next i
.Value = vArr
End With



.

 

[Bernie Deitrick]

Tod,

Using the worksheet is a good way:

If column B is blank:

With ActiveSheet.Range("B2:10000")
.Formula = "=A2-INT(A2)"
.Copy
Range("A2:A10000").PasteSpecial (xlValues)
.Clear
End With