Hi,
Update: turning the calculation to manual worked for 15k rows data but does not work for 60k rows of data. Any other way to speed up the loop?
I have the code below run through 15k rows of data. its currently taking very long time (more than 30mins) to run this code. Is there anyway to speed this up? Thanks in advance.
Please Login or Register to view this content.
Last edited by reiiling; 02-07-2019 at 06:49 AM.
Can you post a small example file so we have something to work with.
Remember to desensitize your data.
Avoid using Select, Selection and Activate in your code. Use With ... End With instead.
You can show your appreciation for those that have helped you by clicking the * at the bottom left of any of their posts.
I see you have turned off screen updating, what if you set the calculation mode to manual as well?
Application.Calculation = xlCalculationManual
if there are other formulas in the workbook it may speed things up
the attachment does seems to be working for me. So I have attached image here.
I have cleared unused columns for this code, and renamed everything.
sheet1.PNG
sheet2.PNG
Did you try, as davsth said, turn off calculation?
Put on start of macro:
Formula:
Please Login or Register to view this content.
and at the end:
Formula:Please Login or Register to view this content.
Happy with my answer * Add Reputation.
If You are happy with solution, please use Thread tools and mark thread as SOLVED.
Attach a sample workbook (not a picture or pasted copy). Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.
Remember to desensitize the data.
Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.
Select your file and click Upload.
Close upload window and Submit Reply.
Thank you davsth! it worked perfectly.
Yes, thank you! its working now. but it made the sheet [Not responding] when its processing the sheet.
90% of my sheets not responding when I am grabbing data from external sources :-)
it only worked for 15k rows of data, now I'm trying to adapt the same method for 60k data and its running very slow again. is there any other way to speed this up?
Untested
Please Login or Register to view this content.
Hi,
maybe speed comparison of different objectes can be useful for you:
https://stackoverflow.com/questions/...yed-collection
Jacek
thank you, but unfortunately this code does not calculate.
Formula not support and not responding with large rowOriginally Posted by reiiling
I am has test with 500.000 row
And code not responding you should be use array or dic like jindon code
"ThankyouFor Attention * And Your Help!!"
jindon's code didn't do anything for me.
Sorry to say this but i hardly believe that:I have the code below run through 15k rows of data. its currently taking very long time (more than 30mins) to run this code. Is there anyway to speed this up?
Speed always depend on your PC Configuration.
check your PC's RAM and Processor capacity.
Regards,
MohanS
"Perfection is not attainable, but if we chase perfection we can catch excellence." - Vince Lombardi
You can simply say thanks by clicking "*Add Reputation" icon
Upload a workbook, otherwise wasting time.
@ jindon
Already asked that in post#2 & post#6 dated 01/31/2019.
@ reiiling
Attach a sample workbook (not a picture or pasted copy). Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.
Remember to desensitize the data.
Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.
The original file is 3MB, I have deleted some data. The "Label" column should be unique serial numbers, I have it as A,B,C,D to reduce down the file size.@ jindon
Already asked that in post#2 & post#6 dated 01/31/2019.
@ reiiling
Attach a sample workbook (not a picture or pasted copy). Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.
Remember to desensitize the data.
Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.
So, you don't know how to use my code...
See the attached.
Thank you jindon for the code.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks