What is the fastest way to copy large amounts of data in Excel?

Resolve Extremely Slow Pasting Into Excel TablesMarch 7, 2020 Chris NewmanStop The Madness!One thing that has always annoyed me with Excel Tables, is how slow it can be to paste la

What is the fastest way to copy large amounts of data in Excel?

Resolve Extremely Slow Pasting Into Excel TablesMarch 7, 2020 Chris Newman

Stop The Madness!

One thing that has always annoyed me with Excel Tables, is how slow it can be to paste large amounts of data into them. This seemingly stems from an inefficiency with how an Excel Table Object resizes itself (one cell at a time). This inefficiency has lingered for years, and after waiting minutes to paste new data into tables as I update monthly files, I finally set out to solve this issue on my own.

How To Manually Resolve This Issue

Before I was able to come up with a programmatic way to solve this issue, I had to understand the root cause. What ultimately speeds up pasting data into an Excel Table, is expanding the table size prior to pasting the data. This requires figuring out the size of your new data set and adding/removing rows/columns to get your destination table the same size as your source data.

This solution is not difficult to do, but it can become cumbersome if you are updating a lot of Tables every month (like I currently am). So lets look at how I created a VBA macro to solve my issue, so now I only have to click a button to quickly paste my data into my Tables.

How To Automatically Resolve This Issue

What Does This Code Do?

This VBA code is meant to replace the need of hitting your paste button. Prior to running the code, you will want to make sure you have copied your data to the Clipboard and have at least one cell selected in your destination Table (the table you wish to resize and paste).

You will first be prompted to indicate if your copied data includes your Header Row or if it only contains data within the data. After you have made your indication, the code will proceed to either shrink or expand your table and paste the values into the Table object.

How Does This Code Work?

1. Test To Ensure There Is Something In The Clipboard
Before we get too far into the code, we want to make sure there is actually data copied to Excels Clipboard! We start the code off by checking to see if anything was copied (xlCopy) or cut (xlCut) to the Clipboard. If this test fails, we notify the user and exit out of the code.

2. Determine The ActiveTable
The next test we want to do before getting too far is ensuring our user has selected a cell within a table. Luckily, I had created this little functionality a while back and was able to grab the code from my VBA with Tables Guide. If the user happens to not have selected a valid location for us to locate their desired destination Table object, then we must exit the subroutine.

3. Read The Copied Data From The Clipboard
To access the Clipboard, we are going to need to utilize the GetFromClipboard function. The problem is, this function is part of the MS Forms 2.0 Object Library. This library is typically deactivated unless you have a userform created in your project or you have manually gone into the available Libraries list and activated it (VBE > Tools > References).

In an effort to bypass manually activating this library, I am using the CreateObject function and the reference code (that crazy long code) to call and activate the library why the VBA is running. After that piece of code has been executed, we now have access to the entire MS Forms 2.0 library, including the Clipboard object.

4. Calculate How Many Rows/Columns Are Needed
Next, we need to analyze the data we pulled from the Clipboard. When you copy cells to the Clipboard, the data is separated by Tabs (vbTab) and Carriage Returns (vbCrLf). What we can do is count how many carriage returns are residing within the stored data in the Clipboard and that will let us know how many rows we have. We can also count how many tabs are located within the first row of the Clipboards data set to determine the column count.

5. Resize The Table
Now we are to the time-saving part! Since we have now figured out how many rows of data were copied to the Clipboard, we can begin to resize the table before we paste our data.

First, well need to figure out if we need to expand or reduce the size of the table. You can simply do this by comparing the ActiveTables row count to the Clipboards. After that is determined, the code will either prepare to delete table rows or add them with the Resize function.

The one tricky part about this codes is the order in which the resizing process takes place. If you delete rows or columns, Excel will automatically clear the clipboard leaving you with nothing to paste. Hence, why it was important to handle the minimizing the tables footprint separately from any expansion activities.

6. Paste The Data (Coded as Paste Values Only)

Finally, after the Excel Table is resized to our likely, we can paste in the data. Ive chosen to paste values only, but you can modify the code to use whatever PasteSpecial type youd like.See this content in the original post

I Hope This Helped!

This was a pain I was dealing with for far too long. And in my humble opinion, this is a HUGE problem that Microsoft needs to fix. Many people praise the versatility that Excel Tables give you, but if they are a pain to use with large data sets (and our data needs are only growing), why go through the hassle?

What are your thoughts on this? Let me know in the comments section below!


About The Author

Hey there! Im Chris and I run TheSpreadsheetGuru website in my spare time. By day, Im actually a finance professional who relies on Microsoft Excel quite heavily in the corporate world. I love taking the things I learn in the real world and sharing them with everyone here on this site so that you too can become a spreadsheet guru at your company.

Through my years in the corporate world, Ive been able to pick up on opportunities to make working with Excel better and have built a variety of Excel add-ins, from inserting tickmark symbols to automating copy/pasting from Excel to PowerPoint. If youd like to keep up to date with the latest Excel news and directly get emailed the most meaningful Excel tips Ive learned over the years, you can sign up for my free newsletters. I hope I was able to provide you some value today and hope to see you back here soon! - Chris

Discover more from TheSpreadsheetGuru

Video liên quan