• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 519
  • Last Modified:

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

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
calibreinc
Asked:
calibreinc
  • 8
  • 5
4 Solutions
 
redmondbCommented:
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
 
redmondbCommented:
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
 
NorieVBA ExpertCommented:
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
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

 
calibreincAuthor Commented:
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
 
redmondbCommented:
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
 
calibreincAuthor Commented:
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
 
redmondbCommented:
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
 
calibreincAuthor Commented:
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
 
redmondbCommented:
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
 
redmondbCommented:
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
 
calibreincAuthor Commented:
"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
 
redmondbCommented:
Don't forget to change all cInt's and the Integer Dim's.
0
 
calibreincAuthor Commented:
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
 
redmondbCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

  • 8
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now