I am using the below macro to copy the rows (Starting from Row 2 excluding header row) to another sheet (Starting from Row 2 excluding the header row). I have a huge number of rows which i need to copy to the new sheet (crossing 1.5 Lakh records) which is probably causing "Out of Memory Error: 7" to occur when i execute the below code. Can anyone suggest how we can avoid this issue?
Please Login or Register to view this content.
Last edited by Pavan Renjal; 02-27-2014 at 05:56 AM. Reason: Code tags used which were missing earlier
Hi, Unfortunately your post does not comply with Rule 3 of our Forum and with 73 posts you really should kniw this. RULES. Use code tags around code.
Posting code between [CODE]Please [url=https://www.excelforum.com/login.php]Login or Register [/url] to view this content.[/CODE] tags makes your code much easier to read and copy for testing, it also maintains VBA formatting.
Highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found here
(This thread should receive no further responses until this moderation request is fulfilled, as per Forum Rule 7)
Richard Buttrey
RIP - d. 06/10/2022
If any of the responses have helped then please consider rating them by clicking the small star iconbelow the post.
Hi Richard, I have made the changes to my post. I missed those tags earlier. Thanks for notifying!Originally Posted by Richard Buttrey
Hi,
Very occasionally I've encountered this when dealing with over 100,000 rows. The solution is to break the copying down with a loop so that a manageable subset of records are copied each time. You'll just need to experiment to see the maximum. Start with say 50,000 and see if a loop counter of 3 will copy the 1.5 lakhs.
Hi Richard, but it seems like the following code is copying the entire data. How can i split the Rows using the below code:
Please Login or Register to view this content.
Is there any other way that i can copy rows to split data?
Hi,
untested but the general approach I'd use would be:
1. Create a loop counter upper value variable (say lCounter) and set the value to be
lCounter = Int(sws.Range("A2:A" & sws.Range("A" & Rows.Count).End(xlUp).Row).Rows.Count)/50000 + 1
2. Create a loop counter variable, say x
3. Within the loop i.e.
For x = 1 to lCounter
change the sr variable. e.g.
Set sr = sws.Range("A" & x * 50000 -49999 & ":A" & x * 50000)
Apologies! I am still a beginner in Excel programming but i tried to understand certain things that you tried to explain here. However i still believe that some changes are needed to be done to the below code, please guide me on the same:
Please Login or Register to view this content.
Last edited by Pavan Renjal; 02-28-2014 at 05:26 AM. Reason: correct the Spelling
Since the above mentioned code which does the copying of data needs to have lower bound and upper bound to loop in batches. Correct me if i am wrong.
Can anyone help me on this?
#pavan rewnjal
maybe this is an instance of where you need do the looping through your 100,000 rows the long way instead of trying to do it in "one hit"
the issue isn't hard, the solution is also not that hard... if excel cannot get the required resources because of your computer configuration you only have two options... do it differently or upgrade your computer.
Sorry if this sounds a little harsh but sometimes the old slow methods are actually the right way to go
Jmac
Last edited by jmac1947; 02-28-2014 at 06:10 AM. Reason: spelling and typo issues (again)
Jmac! Thanks for you response but i wanted to know how to alter the existing code for copying data. Since the current code doesn't allow me to set Upper and lower bound. I would like to look for any alternative to copy data.#pavan rewnjal
maybe this is an instance of where you need do the looping through your 100,000 rows the long way instead of trying to do it in "one hit"
the issue isn't hard, the solution is also not that hard... if excel cannot get the required resources because of your computer configuration you only have two options... do it differently or upgrade your computer.
Sorry if this sounds a little harsh but sometimes the old slow methods are actually the right way to go
Jmac
Hi Pavan Renjal,
maybe so
Please Login or Register to view this content.
Hi Nelam, Thanks for your response! The code seems to be working fine without throwing any error (Out of Memory Exception). But somehow the date columns are not getting copied and these columns are filled with the next column data leaving last 2 columns blank!
There are 2 date columns placed somewhere in the middle of the column range.
Could you attach a small sample of your file, where the incorrect data copying
Hi Nilem, I have tested it again. The code works just fine!I missed some changes in the code because of which i was getting undesired results.
Thanks a ton!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks