• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

VBA Code Efficiency

AAP

Member
Hi Everyone,
I need help to optimise my macro written. Actually this macro uses index and match functions. I have a database workbook of around 1,000,000 rows from which the macro searches to found records and paste in a new file. The new file is usually contain data of 100,000 + records.. It takes very long time. Someone suggest me to use memory instead of transact between sheets but I don't know how can I make it efficient.
Records to find is under Pink heading and records to placed after search is under yellow heading. Sample macro file uploaded. The database file is around 50 megabyte and therefore cannot upload.
 

Attachments

  • Rydding Mal New.xlsm
    144.4 KB · Views: 11

Hi,

the more efficient is to use worksheet function advanced filter,
result in a glance in very few code lines !​
 
Last edited:
Hi,
No idea what you mean, Can you provide any sample file with code in which you used advanced filter function so that I can implement to my project. Many thanks
 
How is the Pink area filtered by to get the Yellow records?
What is the criteria?
 
Hi Hui,
The column named [MPN] in the pink area is the column which is used to lookup values in database. Filtering has nothing to do with my project at the moment because that will be the next step. Actually my objective here to vlookup [MPN] towards my database which is extremely big. Second, the file I have uploaded is contains just 2200 rows but actually I receive files with more than 50,000 rows to vlookup with the database. Therefore instead of using vlookup function in vba I used index and match combination to retrieve records and placed in the yellow area but still it lacks the speed and I have to wait 1 hour or more to complete search process. On the web I found a comment that if you minimize traffic between vba and worksheet than your search process would be significantly increase. Article recommend read the data once and save it in the memory than to reread it each time. I didn't completely understand but I believe this indicates me to use arrays instead. But don't know how to use it.
 
Sorry to everyone if my vba statements misguiding. I need to optimize statements up until [Next Counter]
 
Hi ,

Can you upload at least the skeleton workbook named :

MK Q4 2014.xlsb

If you wish you can remove all of the records except for say 100 or even 1000 , and then upload the file , so that we get an idea of the layout of data in that file.

Narayan
 
I'm with Marc, I think Advanced Filter is the way to go. Since you have a list of Named/MPN, you would treat this as your criteria range. You already have a source. Just need to define destination range. Note that for Adv filter to work, column header names must be identical. Check out the attached for an example, or read this link:
http://www.contextures.com/xladvfilter01.html
 

Attachments

  • AdvFilter Example.xlsm
    20.5 KB · Views: 2
Many thanks, here is the file with just 10,000 records. This is now converted into .xlsx from .xlsb because that format is not allowed to upload.
 

Attachments

  • MK Q4 2014.xlsx
    372.3 KB · Views: 2
Just as a tip, you can also load ZIP files to the forum, with any type of extension within the ZIP.
 
With both of your files opened, here's what I did:
destWB = "Ryding Mal New" file
sourceWB = "MK Q4 2014" file

in destWB, added a worksheet. Put these column headers
upload_2015-2-20_10-5-35.png

With that sheet active, go to Data - AdvancedFilter.
Select "Copy to another location"
List range, go to sourceWB, select col A:H
for Critria range, go to destWB worksheet DB, select col N
for Copy to, select A1:G1 of new sheet.
Hit ok, and filter finishes in < 1 second.

This is how you do it manually, but as you can see in my earlier file, you can write the code for this with just a few lines.
 
Hi Luke, I don' know if I didn't understand you right
With new sheet active I go to Data-Advanced filter.
I select, copy to another location
List range, select entire Columns A:H of sourceWB file
For Criteria, destWB worksheet DB, selected entire Column N
for Copy, Select A1:H1 of new sheet.
Hit ok
I got results in 128264 rows after I press escape because excel was not responding and query was only 2207 rows, confused me a bit.
 
Hi Luke, tried many times with different ways and used link provided but still not getting the results. Any help!
 
Hi Luke, I found out why advanced filter solution not working in my case. I selected just one MPN code (criteria) to find out the result you might surprise I got 5 results one containing true value and 4 others with similar in the beginning but different at the end characters. Even I have selected unique values.
 
Hi ,

Sorry , but I can do it only tomorrow ; it is late tonight , and I cannot spare the time now.

Secondly , the .xlsm workbook does not have any data which satisfies the filter criteria.

Anyway , if you can wait till tomorrow , I can upload the revised code ; if not , see if Luke is able to help out.

Narayan
 
Hi again,
Please find attached workbook again which does have data which satisfies the filter criteria.
I can wait until I found an efficient way.
Many thanks and good night.
 

Attachments

  • MK Q4 2014.xlsx
    454.3 KB · Views: 3
Hi ,

Can you check how this performs ?

Please make a copy of your files before you run the macro.

Narayan
 

Attachments

  • Rydding Mal New2.xlsm
    135.1 KB · Views: 5
  • Like
Reactions: AAP
Many many thanks Narayan, its 4 times faster then previous module. This is what I wanted. You are genius. Thanks Again.
 
Hi ,

Thanks for the feedback on the speed ; probably Luke or Marc can post the code for the Advanced Filter , which as they mentioned will be even faster.

Narayan
 
Good Day Narayan,
I am grateful for your help and try to learn from you help.
Just for my knowledge what is the difference between these statements and which one is more efficient.
[With ActiveSheet
lastrow = .Range("A1").SpecialCells(xlCellTypeLastCell).Row
End With]

[With mk
lastrow = .Range("A" & Rows.Count).End(xlUp).Row
End With]
 
Hi ,

Thanks for the feedback on the speed ; probably Luke or Marc can post the code for the Advanced Filter , which as they mentioned will be even faster.

Narayan

Good Day Narayan,
I am grateful for your help and try to learn from you help.
Just for my knowledge what is the difference between these statements and which one is more efficient.
[With ActiveSheet
lastrow = .Range("A1").SpecialCells(xlCellTypeLastCell).Row
End With]

[With mk
lastrow = .Range("A" & Rows.Count).End(xlUp).Row
End With]
 
Hi ,

There are two differences :

1. The first statement treats the entire range of data as one entity ; thus even though you are referring to column A , if the last visible cell is Z1129 , this will return the value of 1129 for lastrow.

2. The second statement specifically looks at column A ; so if column A has data till row 10 , it will return the value of 10 for lastrow , even though some other column in the range may have data till row 1129.

Thus , the second statement should be used only if you are sure the column which is used does have data in it , and it has data where other columns may or may not have data.

At the same time , the first one is also not very reliable.

See this link for more information :

http://www.rondebruin.nl/win/s9/win005.htm

It all depends on how well you know your own worksheet ! If you are sure there will not be any problem using the first , use it ; the same goes for the second also.

Of course , as far as efficiency goes , I think both are equally efficient.

Narayan
 
Back
Top