• 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.

Fastest way to filter data in Excel 2013

T

Tom A

Member
Hi,

What is the fastest way to filter data in Excel using VBA?

I have a worksheet with a list of events. I am using auto filters in VBA to filter the data but it is very slow especially when I open my spreadsheet as it sorts and filters the data at that point.
I have named ranges for each of the columns in the worksheet and there are a few formulas as well. I would like to increase the size of the named ranges to 50000 but that slows down my spreadsheet too much.

Any suggestions would be appreciated.
Thanks
 
Couple of options here.

1. Advanced filter (not in place but export to new sheet)
2. Pivot table. However, not on the same workbook. In separate workbook, just using original as data source.
 
I would like to filter the data in place when possible. Do you know if Ado will let me do this and how fast that is?

Chihiro said:
Couple of options here.

1. Advanced filter (not in place but export to new sheet)
2. Pivot table. However, not on the same workbook. In separate workbook, just using original as data source.
Click to expand...
 
Advanced filter can filter in place.

How fast will depend on what you are filtering (data only, or mixed with formula) and also on how many dependent calculations you have downstream.

You can try setting calculation mode to manual and see if it makes difference as well.
 
Is there any way to run an advanced filter from VBA directly without having to insert the filter criteria on a row above my data in the worksheet?

Chihiro said:
Advanced filter can filter in place.

How fast will depend on what you are filtering (data only, or mixed with formula) and also on how many dependent calculations you have downstream.

You can try setting calculation mode to manual and see if it makes difference as well.
Click to expand...
 
You must log in or register to reply here.
Back
Top