Thursday, January 31, 2019

Fastest Method to Copy Filtered Data in Excel VBA


Yesterday in my post,

https://www.facebook.com/groups/ExcelFreelancersGroup/permalink/2469040236459183/

related to 'Copy the "Filtered" Data from Sheet1 to the Sheet2' and Mr. Mehmet, asked the following thing in addition to the provided solution and here was his query

"Many of Excel users are using excel as Database and they are facing huge datasets such as 500k rows and 100 columns. Could you please provide a faster way to copy and paste data such as arrays or advanced filter or anything that you would suggest us."

****SOLUTION****

Now, I think Mr. Paul Kelly has your answer with such great detail as never seen or experimented before. I would like to thank him for his effort to get such figures which are mentioned in the image attached to this post.

So the basic query of my friend Mehmet is to know all about which method is Fastest of all.

So Mr. Paul has conducted his own speed test to know which method is really quick in the average scenario.

Here is his post in his words

------------------

"These speed tests on copying and filtering data really surprised me.

I didn't think Advanced Filter would be the fastest method.

The columns headers are the number of rows of data in the test.

The values are the time in milliseconds to filter and copy the results to a worksheet."



------------------

Thank you so much, Mr. Paul Kelly, again for these surprising data and hope at least we have found the best method to perform such a task.

- Kamal Bharakhda


IsValidPasswordString Function

'Following function will verify if the password string contains following characters or not? Rem : List of Characters Group - ASCII Rem ...