How to speed up a macro to filter data

**JOAO12** · 08-22-2014, 09:57 AM

Hello everybody,

In a workbook, called “Filter.xlsm”, half of the cells in Range(“A1:AL1048576”) is filled with data and the other half is blank. I run the following macro to filter this worksheet and to count the number of filered rows. It takes 20 seconds to complete one filtering cicle with 15 fields. It seems to be fast, but as I have to filter this worksheet 1000 times, this macro takes more than 5 days to be finished. Is there a way to speed this macro up? I have a quad-core processor (QX9770) and the CPU usage is stuck at 25%.

Please Login or Register  to view this content.

Any help would be appreciated.

John

**TMS** · 08-22-2014, 10:26 AM

Have you really got over a million rows of data? And do all 1000 criteria ranges have numbers in them?

Would you not be better taking each row in the criteria sheet and "building" a criteria range for an Advanced Filter rather than using AutoFilter criteria?

Just some thoughts

Regards, TMS

**JOAO12** · 08-22-2014, 10:54 AM

Hi TMS,

Half of cells from Columns "A" to "AL" (workbook "Filter.xlsm") are filled with data and the other half are blank.

Yes, all 1000 criteria ranges have numbers in them. They are needed to calculate the 15 fields used to filter workbook "Filter", by adding "13" to its value.

In the advanced filter, can I determine that the 15 fields to be filtered are the values of the 15 cells in the criteria sheet added to the number "13" ?

Regards,

John

**watersev** · 08-22-2014, 11:02 AM

hi John, the speed issue you are facing is not linked to the computer power but to the approach being used. Can you post sample file data to play with? It would be also helpful to have expected result on the data provided.

**JOAO12** · 08-22-2014, 11:12 AM

Hi,

In fact, all I need is to count the filtered rows of workbook "Filter.xlsm" using 15 fields.

Each one of the 15 fields is determined by adding the number "13" to the corresponding cell value of range("C" & x & ":Q" & x), being x=1 to 1000 (workbooks "Criteria.xlsm").

The code mentioned in the first post do this, but as I have to work with a big amount of data, it takes days to finish this macro.

Feel free to change the macro. As I said before, my goal is to count the filtered rows of workbook "Filter.xlsm" using 15 fields.

Thanks,

John.

**Norie** · 08-22-2014, 11:17 AM

If you want to count based on criteria could you not use formulas, eg SUMPRODUCT?

**TMS** · 08-22-2014, 11:17 AM

Please Login or Register  to view this content.

I would envisage the 15 fields being copied to an Advanced Filter Criteria range. So I don't see any problem adding 13 to each of them as part of the process.

Regards, TMS

**JOAO12** · 08-22-2014, 11:31 AM

I am searching how to write an advanced filter code, but up to now I have no idea to do so.

TMS, could you please change the autofilter code to the advancedfilter code?

Thanks,

John

**JOAO12** · 08-22-2014, 01:33 PM

Hi TMS,

I am posting two sample files: Filter.xlsm and Field.xlsm.

Field.xlsm has the column "C" up to "Q" filled with numbers. These numbers will be added to 13, in order to calculate each one of the 15 fields of the workbook Filter.xlsm that will be filtered. Note that the filtering criteria is that the cell be blank.

Any help in making this macro faster would be appreciated.

Regards,

John

**JOAO12** · 08-22-2014, 02:46 PM

Hi watersev,

I have uploaded two sample files.

Any help would be appreciated.

Regards,

John

**watersev** · 08-25-2014, 05:41 PM

hi John, please check attachment, Field and Filter data are joined in one workbook. Press "Run" button or run code "test".

**watersev** · 08-29-2014, 04:45 AM

hi John, please check attachment. This would eliminate duplicate rows from results.

**JOAO12** · 08-29-2014, 07:30 AM

Hi Watersev,

Your macro works perfectly. Thank you very much for your efforts on helping me with this macro. You did an excellent job.

John

**watersev** · 09-04-2014, 02:48 AM

hi John, another option, please check if it does what you want and its time of execution. Press Run button on Filter sheet or run code "test1"

How to speed up a macro to filter data

LinkBack

Thread Tools

Rate This Thread

Display

How to speed up a macro to filter data

Re: How to speed up a macro to filter data

Re: How to speed up a macro to filter data

Re: How to speed up a macro to filter data

Re: How to speed up a macro to filter data

Re: How to speed up a macro to filter data

Re: How to speed up a macro to filter data

Re: How to speed up a macro to filter data

Re: How to speed up a macro to filter data

Re: How to speed up a macro to filter data

Re: How to speed up a macro to filter data

Re: How to speed up a macro to filter data

Re: How to speed up a macro to filter data

Re: How to speed up a macro to filter data

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Speed up macro for copying data from another workbook

[SOLVED] Macro for transfering data, Help with improvement of speed

[SOLVED] How to speed up deleting duplicates using a filter

Speed Up Automated Pivot Filter

Optimize/Speed up macro in large data file when copying data and pasting betw. wbooks

Bookmarks

Bookmarks

Posting Permissions