Hi,
I have macro it will compare data in sheet1 and sheet2.Then it will load unmatched records to another shett.
But i have some lakshs of records approx 1lakhs records.When i run macro it is taking so much time.
How to improve the performance of macro.It is not possible to get results with in minutes.My macro taking alomost 2 to 3 hours finally it is hanging.
If possible, please share the code...
If your problem is solved, then please mark the thread as SOLVED>>Above your first post>>Thread Tools>>
Mark your thread as Solved
If the suggestion helps you, then Click *below to Add Reputation
Code? ....
Ben Van Johnson
Below is the code,
below is the code
This code is developed by one of the forum member.Thanks for his help.Only problem is i have lot of records it is taking time and finally hanging.If you have less number of records
Please Login or Register to view this content.
like 20000 ok but i have nearly some lacs of records
It is a cryptic coding to my eyes
I am stepping back
If It is not possible with the same code.any one can provide another code for speed up the process
You could try something simple like stopping & starting interfering things like screenupdating etc. If you need to calculate when the macro's running, comment out Application.Calculation lines
Cheers
Please Login or Register to view this content.
Hi,
A few things that may help to improve the performance:
1. store concatenated values in the next free column on sheet 1, 2. This way avoid multiple loops trough all cells in the respective row. Also I would use one loop to construct a string that will use with CONCATENATE formula with calculations stopped (see also 2.)
2. Use vlookup with calculation stopped and then calculate (it will take some time of course). Convert formulas to values after calculation (single statement)
3. no need to loop through each cell in the row in sheets 3,4 to populate with the values from sheet 2, 1. Use one statement for the entire range. Note that for me it look like you overwrite the value in the last cell in the row with "Not present in sheet"
4. I don't see the use of Var array.
Just my two cents. without data it's hard to suggest different implementation
Last edited by buran; 03-21-2014 at 04:30 AM.
If you are pleased with a member's answer then use the Star icon to rate it.
To attach a Workbook
Do not post a workbook with thousands of rows of data, just enough to step through the macro.
(please do not post pictures of worksheets)
- Click Advanced (next to quick post),
- Scroll down until you see "Manage Attachments",
- Click that then select "add files" (top right corner).
- Click "Select Files" find your file, click "open" click "upload"
- Once the upload is completed the file name will appear below the input boxes in this window.
- Click "Done" at bottom right to close the Attachment Manager.
- Click "Submit Reply"
Explain what the macro is actually doing with the data...
By the way, what is: lakshs in English??
I was curious too and googled it. lakh=100000 http://en.wikipedia.org/wiki/LakhOriginally Posted by protonLeah
The reason why its take so long is it is loading and deleting the data over and over. toravietl you need to use arrays so you don't keep reloading the same columns over and over.
toravietl is loading all values of each column in sheet 1 then compares it against each column of data in sheet2, if it finds a match it moves onto the next column, if it doesn't it puts in sheet 3 in the corresponding column. Then does it vice versa on sheet 4.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks