Clear Filtered Data From Autofilter Result on Table

[Vlad717]

Hello,

I'm looking for ways to speed up my macro that runs repeated autofilter. It takes the contents and moves them to a different sheet based on that result, then on to the next one, and on and on. It was taking anywhere from 10-30 minutes to run each autofilter.

Here is an example of one. Nothing special really.

Please Login or Register  to view this content.

I found that putting the data into a table first speeds up the autofilter by a HUGE margin. The bottleneck at this point is now removing the filtered data from the master sheet.

Before, I was deleting the actual rows and while it worked, it takes awhile and I'm looking to make this as quick as possible since its only a small portion of a rather large series of events.

The next thought was to leave the table intact and simply clear the cells of data so the table structure doesn't change. But... I can't seem to clear the contents with

Please Login or Register  to view this content.

Any suggestions?

[dangelor]

Possibly...

Please Login or Register  to view this content.

and if it's really needed, place this after the paste line

Please Login or Register  to view this content.

[Vlad717]

Deleting the rows unfortunately takes too long which is why I'm trying to clear the cell contents instead.

Sorry, I'm not following how stating range.autofilter on its own would remove the contents of the cells though? calling the range.autofilter would just turn it off which in turn no longer has the filtered data selected.

[Vlad717]

Oh I'm a complete dummy. It works perfectly fine.

My problem stems from this

Please Login or Register  to view this content.

I initially had this to find the visible cells in column A and delete the entire row of those visible cells.

Now when I changed it to just clear contents I forgot to update the range from A:A to A:BP. So using specialcells(xlcelltypevisible).clearcontents works perfectly fine in a table! Doh

[dangelor]

Happens to us all sometime...