Iterating 100 cells takes too long_问答_开发者

In my excel VBA code, I need to move some data from a range to another sheet.

As of now, I'm iterating through the range and copying the values like this:

For offset = 0 To 101
        ActiveWorkbook.Sheets(Sheet).Range("C3").offset(offset, 0).Value = ActiveSheet.Range("D4").offset(offset, 0).Value
    Next offset

However, it takes almost a minute to iterate and copy the values for the 100 cells.

Would I be better off using Copy-Paste programatically, or is there a way to copy for the entire range at once? S开发者_JS百科omething like:

ActiveWorkbook.Sheets(Sheet).Range("C3:C102").Value = ActiveSheet.Range("D4:D104").Value

You can read the entire range at once into a Variant array, and then write it back to another range. This is also quick, flickerless, and has the added bonus that you can code some operations on the data if you are so inclined.

Dim varDummy As Variant
varDummy = ActiveSheet.Range("D4:D104")
' Can insert code to do stuff with varDummy here
Workbook.Sheets(Sheet).Range("C3:C103") = varDummy

This I learned the hard way: Avoid Copy/Paste if at all possible! Copy and Paste use the clipboard. Other programs may read from / write to the clipboard while your code is running, which will cause wild, unpredictable results.

Also, it's generally a good idea to minimize the number of interactions between VBA and Excel, because they are slow. Having such interactions in a loop is multiply slow.

So, silly me did not try before posting here. Apparently, I can move data for an entire range this way:

Workbook.Sheets(Sheet).Range("C3:C102").Value = ActiveSheet.Range("D4:D104").Value

Its as fast as copy-paste without the switching of sheets. Iterating through the range using a for loop takes about 45s for 100 cells, while the above two options are instant.

You can speed up code and stop flickering with:

Application.ScreenUpdating = False

'YOUR CODE

Application.ScreenUpdating = True

More: http://www.ozgrid.com/VBA/excel-macro-screen-flicker.htm

Columns("A:Z").Select
Selection.Copy
Sheets("Sheet2").Select
Range("A1").Select
ActiveSheet.Paste

That will copy columns A to Z from Sheet 1 to Sheet 2. This was generated by recording the macro. You can also apply it to ranges with something like this:

Range("D4:G14").Select
Selection.Copy
Sheets("Sheet2").Select
Range("D4").Select
ActiveSheet.Paste

Is this something like what you're after?

If you need anything specific and you can do it manually (e.g. copy and paste), record the macro to get the VBA code for it.

Copy and pasting has a decent amount of overhead in VBA, as does dealing with ranges like that. Its been a while since I have done VBA but if I recall correctly the fastest way to do something like this is to write the values you want into an array and then use the Resize function. So something like this:

Option Base 0
Dim firstrow as integer 
Dim lastrow as integer
Dim valuesArray() as Long
Dim i as integer

//Set firstrow and lastrow however you deem appropriate
...

//Subtracing first row from last row gets you the needed size of the 0 based array
ReDim valuesArray(lastrow-firstrow)

for int i = 0 to (lastrow-firstrow)
  valuesArray(i)=Cells(i+firstrow, COLUMNNUMBER).value
next i

Of course replace COLUMNNUMBER with whatever column it is you are iterating over. This should fill your array with your desired values. Then pick your destination cell and use Resize to put the values in. So if your destination cell is D4:

Range("D4").Resize(UBound(valuesArray)+1, 0).value = valuesArray

That write all the values in the array starting at D4 and going down to as many cells are in the array. Slightly more complicated but if you are going for speed I don't think I have ever come up with anything faster. Also I did this off the top of my head so please test and make sure that you don't cut off a cell here and there.

That OZGrid page has very useful info - http://www.ozgrid.com/VBA/SpeedingUpVBACode.htm

In my case, I need the formatting to be copied as well so I have been using this:

Sheet1.Range("A1:A200").Copy Destination:=Sheet2.Range("B1")

but was still having very slow execution - to the point of locking up the application - I finally found the problem - at some point in the past a number of empty text boxes got into my page - and while they were copied each time my code ran they were not erased by my code to clear the working area. The result was something like 4,500 empty text boxes - each of which was copy and pasted by even the code above.

If you use Edit - Go To... - Click on Special - then choose Objects - and you don't see anything that is good - if you see a bunch of objects that you were not aware of on your page that is not good.