Solved

Excel 2007 Copy Range Method fails (1004) at the same spot

Posted on 2013-01-10
14
418 Views
Last Modified: 2013-01-15
I've posted a similar question here years ago and it was answered.  Funny thing is that it's the same issue, but using the solution that was provided way back then and the amount of data is much more.

Basically I have a "raw data" tab that contains rows of data and I'm copying a template to another tab that puts selected columns from that raw tab into an easy to read format.  I've never had this much data to process before.  Excel 2007 XLSB (binary) file I'm sure can handle it, I'll only use about 110K rows when it's done, but it's dying at row 7,946 each time on item 568.  The error it throws states that the copy range method has failed.  I've gone over the modules associated with it to ensure minimal overhead is being used.  Have I hit a wall?

Ctrl-M launches the macro which wipes the report tabs and reinitiates each report.  It dies on following the initial building of the detail tab where it is adding rows (add_detail_rows) which is initially called by copy_detail_template.
FY13-AKZ-20130104-STK.xlsb
0
Comment
Question by:calibreinc
  • 8
  • 5
14 Comments
 
LVL 26

Expert Comment

by:redmondb
ID: 38765142
Hi, calibreinc.

It's being running for about ten minutes so far - roughly  how long does it take to crash?

(BTW, for anyone else trying this, be careful - the clipboard is heavily used!)

Regards,
Brian.
0
 
LVL 26

Expert Comment

by:redmondb
ID: 38765311
calibreinc,

OK, I cancelled it after half an hour. It's possible that the run was broken by me using the clipboard, however I think it more likely that it's inefficiencies in the Add_Detail_Rows macro.

The file is trivial in size compared to what an xlsb can handle - my biggest is 70mb and people here have mention 150MB+.

Couple of points...
(1) Please re-run the macro and when the error occurs take a screen shot - please ensure that the failing line is shown. Select Debug and in the Immediate Pane enter the following...
?strCopyRange
?strNextRow

What are the values?
(2) I strongly suspect that copy_detail_template() can be significantly improved. Are you interested in spending time on this?

Regards,
Brian.
0
 
LVL 33

Expert Comment

by:Norie
ID: 38765428
Which sub(s) are causing the problem?

I notice in all of them you have unnecessary Selects/Selection/Activate etc, that type of thing slows code down.

Also, in one of them you have  page setup code - that always takes time.
0
 

Author Comment

by:calibreinc
ID: 38766912
Yes, it uses a lot  of clipboard, but it's cleared immediately after the copy.  At one point I was clearing it prior to and immediately following the line of execution to ensure there wasn't any stray "issues".  Yes, I know the other modules aren't a elegant, but I clean them up whenever I need to modify them for whatever reason.

Worse case I'll have to drop the copy method and have to go directly to entering formulae and formatting into each cell via code, which would make it worse whenever I need to make changes to the layout of the report.

The process dies about twenty minutes in for me, it's never gotten any further.

I miss stated about the "size" of the file, I meant the volumn of data (number of rows) having to process.  I've got spreadsheets well over 200MB, for other projects.
copy-error.PNG
error-line.PNG
0
 
LVL 26

Assisted Solution

by:redmondb
redmondb earned 500 total points
ID: 38766942
Thanks, calibreinc.

Yes, I suspected that line, hence my request for the two variables - please post them here!

I'm going out for a couple of hours, so I'll start the macro now and see what happens.

Edit: Update: OK, I just got back and there's an Overflow error when strNextRow is 32768 (please see below). The problem is that you're trying to use cInt() to convert 32768+3 to an integer - but the maximum value for an integer is 32,767. A couple of points...
(1) I'll replace all cInt and Integer Dim's by their Long equivalents and try again.
(2) The fact that I apparently got past your point would appear to suggest that it's a capacity problem on your PC - however,that's extremely unlikely to result in the macro failing on the same line. We really need to know what values those two variables have!
 
Regards,
Brian.
FY13-Overflow.jpg
0
 

Author Comment

by:calibreinc
ID: 38767855
strCopyRange is just that a range built "dynamically" in the calling sub copy_detail_template based on the first row and last row of the data range of the first item.  It is subject to change, thus the reason is built

strNextRow is the next  target row in sequence based on the row interval, again based on the sub copy_detail_template.

I have overcome the copy issue by using the following:
Range("A" & strNextRow & ":CA" & CStr(CInt(strNextRow) + intRowInterval)) = Range("A8:CA22").Formula

However, I'm now working on the formatting, from what I've found, so far, I may have to "hard code" the formatting for each cell and/or range of cells.  Again, I'm looking for a  way to perpetuate the formatting on the same page from each report group to the next with minimal overhead.
0
 
LVL 26

Expert Comment

by:redmondb
ID: 38767916
calibreinc,

No, I wasn't looking for a description of the variables - I asked for their values at the point where the macro failed.

As you're happy that you've resolved the question, please close this question (hit the Request Attention button and either request that it be deleted or that your last post be selected as the solution). Then raise a new question for your formatting query.

Brian.
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 

Author Comment

by:calibreinc
ID: 38768616
strNextRow  is 7,946 when it dies.  strCopyRange  is A8:AC22...

My changes by "hard coding" the format, made no difference.  It still dies at the exact same spot.  The clipboard is clear, there is no copying in that sub or the functions called.

I've attached my revision.  It's dying at the first line at the start of the loop at the same point as it was copying.  The error is still 1004...
FY13-AKZ-20130104-STK.xlsb
error-line2.PNG
0
 
LVL 26

Assisted Solution

by:redmondb
redmondb earned 500 total points
ID: 38769126
calibreinc,

Again my results are consistent - it took just over an hour to get to row 32,768 and the Integer limit.

At that point I went into Debug and changed all of that macro's cInt's to cLng's and resumed the run. It's still going.

Its behaviour for me is consistent and is exactly what's to be expected. I'm deeply puzzled by your consistency, so Edit: I've paused the run in Excel 2010 and it's now going in Excel 2007.

Edit2: Good news - it happened to me in 2007.

Brian.
0
 
LVL 26

Assisted Solution

by:redmondb
redmondb earned 500 total points
ID: 38769374
calibreinc,

Please see my last post.

At the point where the error occurs, the Impact_Detail sheet (at least) is corrupt. (For example, if I select A7946:CA7960 and hit the delete key or do a ClearContents or key a character and hit Ctrl-Enter the change only goes as far as C7947.)

I believe that the file you posted above was the result of a failed run (simply because it stops in C7947). Do you have a clean version of the file?

Thanks,
Brian.
0
 

Author Comment

by:calibreinc
ID: 38770620
"Clean" version, probably not, but I will; I'll rebuild it and try it again.  Back of my mind I thought it could have been corrupted.
0
 
LVL 26

Accepted Solution

by:
redmondb earned 500 total points
ID: 38770648
Don't forget to change all cInt's and the Integer Dim's.
0
 

Author Closing Comment

by:calibreinc
ID: 38779350
Thanks for the input, everything contributed to organizing my solution.  Basically, I rebuilt the template spreadsheet, made the code changes, then finally UPGRADED to Office 2010.  With that I ran the process overnight (over 15 hours) and it still didn't finish.  When I broke it it was about 30K rows short of finishing.  In the end I input a caveat that anything over 1,000 items to not create that tab, which is also done for other client reports.  This amount of data is unprecedented and may not be seen again, but it's good to be prepared.

Again, thanks for the assist!
0
 
LVL 26

Expert Comment

by:redmondb
ID: 38779556
Thanks, calibreinc.

As I indicated earlier, there is huge scope for dramatically improving the run time. Do yourself (and your users!) a huge favour and post a new question!

Regards,
Brian.
0

Featured Post

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

This article will show you how to use shortcut menus in the Access run-time environment.
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now