Any Faster Way to Copy/Paste this range? (I have searched and searched and searched)

Hello Everyone,

So I'm at a loss here after numerous searches and faux solutions. This is similar to my last thread except I've narrowed down the time delay in my code to this:

Please Login or Register  to view this content.

I know this is a large range, but I can't get the calculation faster than 37 seconds on my computer (and I know it will be slower on some of my end users' machines).

The whole range isn't necessary, and I've tried using just A1 and Current Region to capture just the data pasted, but everything I've tried is 37 seconds or slower. The actual range will be closer to 3000 rows in length but I want to future-proof this against a longer dataset (I also tried the code at 5000 rows and its the same damn speed).

Can anyone think of a faster/more efficient way to copy the contents of this range, or the CurrentRegion's range starting at A1 and pasting it to the destination's range?

Please Login or Register  to view this content.

Hey AB33 & AlphaFrog,

Thank you both for the incredibly fast replies!

Unfortunately both produced relatively the same result. I can upload the workbook (probably best through a drop-box link since its too big for the forums) if you think it would help you look into this.

There are a fair amount of formulas related to the pasted data but I can't see why they would affect the copy/paste rate being disable through the macro first.

I even tried a much more simple range "a1:g2" and even that took its fair bit of time. Anything beyond a single row and its molasses.

If you have about 5000 rows, but the file size is big, I suspect the data type is not clean. The data might have been imported from outside, so there might be unseen dirty characters hidden to your eyes. What is the size of the file( In KB)?
There might be rows which appear to be blank to your eyes, but they might not blank, so you might think your data goes down to 5000 rows, but might go further down.

Hey AB,

The data comes from an online-generated reported. The user copies the entire sheet and pastes into the dashboard sheet named datapaste. There is an extra shape or two, and some headers & columns that aren't necessary, so I first have a macro to clean up the data. It deletes the entirerow for everything above the header-row (which is usually around row 9 or 10, so that becomes row 1) and then deletes 3 or 4 columns based on their headers (because that data isnt necessary for the dashboard).

Since I have the user copy the whole sheet from the online report, I wanted to use another sheet called dataparse (I know its close to the other : /) where the cleaned up data can then be referred to on all other sheets and/or be sorted according to the users wishes.

The file size:

Pre-you asking: 2.82 mb (2,820 kb)

After Deleting the datapaste sheet (but not the dataparse): 2.72 mb (2,720 kb)

After using GoTo Special-> Shapes and deleting all shapes on datapaste (including the 3 buttons I had to run macros): 2.81 mb (2,810 kb).

The size is large because there are 21 sheets in addition to the datapaste and dataparse tab. The latter will be hidden before release to the end user.

Let me know if you need any other info and thanks!

I had suspect it. I had experience it. This is my suggestion:
Once you import the data in to excel, before you do any work in excel, you should
Copy the entire data and paste VALUES ONLY in to new sheet. Delete the old sheet completely and save the file.
You need to do the same copy and paste values ONLY for all sheets if they have been imported. I am not convinced for 21 sheets, you get in to MB, rather than KB.
Excel is not designed to store strings(It is not a database).
Unless you start with clean data, soon or later you will face the same problem with slow ness of data process.

Alpha,

Holy bloody eff. Leaving Calculation on manual takes approximately half a second. Half a second!!!

Now for the million dollar question: How do I re-enable calculations without it taking the length it did before? I just tried a separate macro whose only code was to make application calculation automatic and I received the same slow results as before.

Each sheet named 1-20 has five columns of the following formula (with column reference changes to the index range):

Please Login or Register  to view this content.

There is one other column on the sheets 1 through 20 that has this formula:

Please Login or Register  to view this content.

This column has conditional formatting rules (the only CF rules on each sheet) to change cell background color based on value.

These formulas go for about 500 rows on each sheet (but that length may not be necessary).


In the DataParse tab, currently there are two columns of:

Please Login or Register  to view this content.

and

Please Login or Register  to view this content.

To separate out the components of the street address (number versus name) so that the addresses can be part of the sort fields.


There are several other formulas throughout the sheet but those are all the current heavy hitters.

In the DataParse tab, the two Trim codes go from row 2 to row 7500 (but as I said, I could reduce this range somewhat).

Hey Alpha,

Let me think about your most recent thought and determine if there is a good alternative. I'll try to share a pared down version of the workbook to this thread but probably won't get to that until later tonight or tomorrow.

Thanks for your help so far!!

Hi Alpha,

I've reduced the number of formulas per page by a pretty significant amount (at least 20,000 gone, poof, over the entire workbook). Leaving automatic calculation on results in 20 seconds (so already a 17 second improvement with your code and trimming down the formulas).

Attached you will find a workbook that has been reduced so that it will fit onto the forums. The full workbook has 20 numbered tabs in addition to the Master Sheet (which is not built out yet but will have a few graphs-likely pivot charts), the DataParse (will eventually be hidden) and the Data Paste.

I've set the format of the data in the data paste tab to exactly what it looks like when a user would paste the online generated report into excel. Of course I've removed all identifiers, but you'll get the gist (in my test dataset there were 2800 rows of data but that can fluctuate. I trimmed it down to around 1100-1200 to save file space for the upload).

So here's a question:

In column H and I on the DataParse tab, I have the two Len/Trim formulas to separate the address components out. How can I make the macro perform these calculations for only the number of rows there is data for in columns A-G?

Mind you I don't want the formulas to just fill down (although that would be a better alternative to what I have now), I'd like the values just to populate if possible (which will save resources).

Doing this will eliminate another 6 to 8 thousand formulas depending on the size of the dataset.

As always, thanks in advance!

Update: I have since solved the fill-down formula method with the following code added into the Trim macro:

Please Login or Register  to view this content.

I'm back to whether we can shave any more time off without sacrificing too much more of the structure.

Thank you Alphafrog and AB for your contributions to this thread. I appreciate you taking the time to help : ).