[How-To][VBA] Move Data in the Most Efficient Way

[Zer0Cool]

Introduction

In VBA there are many ways to "copy" the contents of a continuous set of cells (henceforth a range) from its source to a destination. The focus of this post is to outline a handful of methods, explaining the upside and downside of each and detailing when to use each. Mostly however I intend to focus on the most efficient and scalable method for doing so as its a lesser known method. I came to these conclusions after years in a career in which at a point I researched the matter in depth and applied that knowledge to helping huge companies that Microsoft recommended the company I worked for to, as to better handle their data in Excel.

Smaller code snippets in this post may be psuedo code, meant to show the premise but not exactly be functional as is.

Feel free to skip around in this post, however I would recommend reading the whole post. My hope is this post will offer a central place for discussion on the matter of simple 1 to 1 moves of data in Excel. If for nothing else so I may link people to this instead of having to explain it for every response of mine on the topic. I hope everyone finds it helpful. Thanks

Table of Contents:

• Introduction
• Glossary
• Methods
• RAR Method
• Resources

Glossary

We need to discuss this topic in like terms. I am going to assume you know the basics of the structure of a workbook (ex: instance/window>workbook>worksheet>range). However I will be using some terms in this post you may not have heard of so I want to define them first so that it is easy to follow when we get to details later on.

• RAR Method - RAR (Range to Array to Range) is a method in which we dump the values in a range into an array variable, then dump the array into another range. This produces the result of "copying" contents from one range to another.
• Range - A range technically can be a single cell or multiple cells. In this post I will use the word to refer to a set of continuous cells on a sheet. If I refer to a single cell, I will instead use the word cell.
• Table - While technically a whole sheet can be thought of as a table, when I use the term I mean the literal feature in Excel that defines a range as a table via Insert | Table. If instead I am referring to a set of cells not using this feature, I would refer to them as a range.
• Scaling - When I mention scaling in this post, I mean how well the code does as the size/dimensions of the range grows. IE: everything works for A1:B2, but most of the methods would fail on A1:Z600000.

Methods

There are a number of methods to accomplish a simple "copy" of a range to another range. How you do so can depend on many considerations; do you need formatting too or just the values? Will the range wind up being large? Will it always be a continuous range? Do I need others to understand my code? Is the speed of the code important to me?

I may not list all methods or all variations/combinations of methods but this should cover the gist of them broadly.

Listed in terms of slowest to fastest methods:

1. For loop using numeric starting variable and numeric end (x=1 to 10) and .Range(x,y) or .Cell(x,y) within. Very slow and scales poorly, but reliable. In smaller ranges it gets the job done and is relatively easy to understand.
   Ex:
   

   Please Login or Register  to view this content.

2. Literally copy and paste (or any variation of paste like paste special). Range().copy and/or Range.Paste. Scales poorly. Also can be prone to issues/errors just like manual copy/paste, IE: out of memory errors, clipboard full, etc. Very easy to understand. Added benefit of being able to carry over formats (can also be a downside, causing duplicated styles, etc)
   

   Please Login or Register  to view this content.

3. For Each loop using Range collection. A bit faster than the above, still scales poorly. Makes working with each cell in a range easier vs numeric For loop. Likely even easier to understand than numeric For. Various ways to move the data using this loop (hence "..."); Feed array, feed another range using a counter, etc.
   

   Please Login or Register  to view this content.

4. Arrays, Dictionary, Collections. Collection is the slowest of the 3, each has its own benefits. Usually these are combined with a loop to feed the values in and a loop to dump them to a destination. They can make acting on the data much faster than in a range (sort, calculations, manipulations, etc.), but are still held back by the speed of looping cells in a range for the purpose of moving data. The specifics of these in this context is beyond the scope of this post however.

RAR Method

This method does not use a loop for the source range or the destination range. It instead relies on the fact that Ranges ARE Arrays, and the 2 are interchangeable. Using a variant variable allows dumping the source range into the variant which automatically makes it an array AND dimensions it for us! We then determine the destination, the catch being that the destination must have the same dimensions as the source, and simply dump the array into the destination range.

This method scales extremely well. In my testing I used 2 files (not attached here due to sheer size), one for source and one for destination. The data set was 100,000 rows by roughly 20 columns. The goal was to move the data to the destination in the fastest way possible. Most methods crashed when attempting this. I set up a timer in a macro to time how long the moving of the data took. On my machine with my sample file it was roughly 4.1 seconds on average. I then increased the number of rows to 600,000 total. Retested and the run time went up to 4.7 seconds on average. 6x the data moved in an extra .5 seconds.

The logic of this method is basically as follows:

• Declare a variant variable to hold the range. ex:
  

  Please Login or Register  to view this content.

• Store a range in the variant variable. ex:
  

  Please Login or Register  to view this content.

• Resize the destination range according to the array bounds and transfer array to range.values. ex:
  

  Please Login or Register  to view this content.

Specific details can be found on the Info page of the attached sample file.

Essentially the variant gets automatically dimensioned into a 2D array by assigning a range object to it (and not the value of the range). We can then use the array to determine the dimensions that the destination range must be and resize the destination range. Its simply then a matter of setting the destination value to the array.

Resources

The attached sample file includes an explanation on the Info sheet as well as a sheet to demonstrate the method using ranges and a sheet to demonstrate using tables.

[Bernie Deitrick]

Why didn't you use Range-to-Range instead of Cell-to-Cell? I.E, change

Please Login or Register  to view this content.

to

Please Login or Register  to view this content.

[Zer0Cool]

In my testing .value = .value experienced a degradation in speed and stability with large ranges. When testing it on a large range Excel would freeze/crash. That is why I am dumping the range into an array in this fashion and then from the array into the destination. I wish I had a real technical explanation of why this works as well as it does but I do not. One would think if a range = array then range to range would be the same or better than range to array to range, but in my testing it was not.

I could add that in as one of the methods, Id say .value to .value is likely the fastest and most stable before the RAR method I have used, so #5 on the list of methods/examples. Its also pretty easy to implement and understand. I forget where the cutoff was in terms of it not crashing, but I would say >100,000 cells in a range and RAR method should be the go to. It will differ person to person as each has different software and hardware, so some may run into issues sooner, later or never possibly using certain methods.

[xlnitwit]

Also

Please Login or Register  to view this content.

I know you said it's pseudocode but I still can't bring myself to type wb.ws.Range(x,5)

[Zer0Cool]

.copy with destination on the same line is functionally no different from using 2 separate lines for copy/paste. Still faces the same limitations as #2 in the list. It is of course easier to write, but also lacks some options you get when using .paste.

Yea I agree when I write "code" like that a die inside a little :P Problem is I just cant account for every variation without making the post absurdly long and even more boring then I imagine it must be for others to read.

[xlnitwit]

In terms of the clipboard and memory efficiency they are quite different. For a single copy and paste, the one line version is preferable.

I’d also recommend .Value2 over .Value for most cases of simple value assignments.

[Zer0Cool]

I guess to be more clear on my part, the 2 line copy/paste is still copy and paste. The single line may be somewhat better, but still faces the limitations of using the clipboard.

I also agree, .value2 is the recommended method vs .value. To be honest I have never seen anyone including me use .value2 though. I guess that extra character is too much work, or I am just too old and stuck in my ways lol. I forget where I saw it but I believe MS has documented .value2 being the better choice.

[xlnitwit]

If you watch the clipboard, the one-liner doesn't add anything to it, unlike a 2 liner. I quite often use Value2, probably because I used to automate Excel a lot.

[Zer0Cool]

I would guess the 1 liner is using the MS Office internal clipboard and not the Windows clipboard, or maybe the other way around. In either case I am positive that it faces similar limits in terms of how much it can copy before becoming unstable.

It would be interesting to see people post results of testing too. Creating significantly large workbook(s) and testing "copying" data between to large ranges. I want to say the sample file I based off of was like ~35MB with 600,000 rows.