Delay when writing cell information to Excel from VBA Macro

  • Thread starter Thread starter Dick HSV
  • Start date Start date
D

Dick HSV

When I loop through in VBA setting a value for a variable the loop goes
fast. When I use cell(i,j).value to put the value in a cell there is about a
1 second delay between each cell. For 250 cells it is a long wait. Is there
any way to control this wait?
 
add
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

to the beginning of your code

and

Application.ScreenUpdating = true
Application.Calculation = xlCalculationAutomatic

at the end
 
Also, read the range directly into a VB array, and if necessary, loop
through the array in memory. Then write the entire array to a range in one
step.

Sample Usage:

Reading:
Dim vArray As Variant ' must be "variant" to read range into VBA array
vArray = ActiveSheet.Range("A1:D10").Value

Writing:
' can be array of any variable type
ActiveSheet.Range("A1:D10").Value = myArray

This will produce a great improvement in execution speed, particularly
writing back to the sheet.

- Jon
 
Back
Top