Can I write an array to a range and only recount the changed cells?

Question

I have a very large array of data that I write in a range. However, sometimes only a few elements of the array change. I believe that since I write the entire array in a range, all cells are recounted. Is there a way to effectively write a subset of elements - in particular, those that have changed?

Update: I essentially follow this method to save recording time:

http://www.dailydoseofexcel.com/archives/2006/12/04/writing-to-a-range-using-vba/

In particular, I have a set of properties that I fill with all the objects (they are cells) with the data that I need. Then I look through all the properties and write the values ​​to the array, indexing the array so that it matches the size of the range that I want to write. Finally, with TheRange.Value = TempArray I am writing data to an array per sheet. This last step overwrites the entire range, I believe that it causes recalculations even in cells whose actual values ​​have not changed.

Answer 1

Let me start with a few basics:

• When you write in a range of cells, even if the values ​​are the same, Excel still treats it as a change and will recalculate accordingly. It doesn’t matter if you turn off the calculation, then the next time the range / sheet / workbook is calculated, it recounts everything that depends on this range.
• As you have discovered, writing an array to a range is much, much faster than writing cell by cell. It is also true that reading a range into an array is much faster than reading cell by cell.

As for your question only about writing a subset of the data that has changed, you need a quick way to determine which data has changed. This is probably obvious, but it must be taken into account, since any of this method will also take some time.

To record only the changed data, you can do this in two ways: either return to recording by cell, or break the array into smaller pieces. The only way to find out if any of them is faster than writing the entire range is to try all three methods and time them with your data. If 90% of the data is changed, recording the entire block will certainly be faster than recording by cell. On the other hand, if the modified data represents only 5%, then the cell might be better. Performance depends on too many variables to give a solution with one answer to everything.