Requesting critique on macro which copy filtered data to new table. = ) Workbook inside.

Hi all,

My basic problem is this: I often filter data and am then interested in compiling statistics or other stuff on this data. This can't be done using ordinary Excel formulas. So, my approach have been copy and paste filtered data to a new sheet. This is time consuming.

With some online sample code, I was able to create a macro which copies my filtered data to a new similar table.

Further, using the Macro Recorder, I was also able to create a macro which deletes the filtered data as the macro above does not clear the table first. I can live with that.

Can I ask some of you guys kindly to take a look and see if this seems okay/correct? Or if there's something that could be done better?

I made a few tests already and it does seem to work correcly.

Thanks in advance!

Best regards.

Elijah

Hi, CK76,

Great improvement! Now I only copy the visible cells as you said.

Much better, I think. Thanks a lot.

But to control filtering and extraction, it may be beneficial to use Advanced Filter Copy method. See link for detail.
https://www.contextures.com/exceladvancedfiltervba.html

My filtering is typically 'dynamic' and not predetermined.

What's described there seemed more relevant if the filtering was decided in advance, i.e., pre-determined?

Best regards and thanks.

Elijah

You are welcome and thanks for the rep

Advanced filter criteria range can be set up with dropdown etc to change criteria on the fly.
For instance, advanced filter can use AND/OR condition combination on multiple columns, where autofilter isn't able.

However, if there is need to visually scan available filter options, then it's better to stick with autofilter.

Originally Posted by CK76

You are welcome and thanks for the rep

Advanced filter criteria range can be set up with dropdown etc to change criteria on the fly.
For instance, advanced filter can use AND/OR condition combination on multiple columns, where autofilter isn't able.

However, if there is need to visually scan available filter options, then it's better to stick with autofilter.

Thanks, CK76.

I will read up on those advanced filters. I'm sure I can find them beneficial in some way. I'm learning new things about this amazing software every day.

Have a good weekend and thanks again!

It seems like it was as easy as updating

Please Login or Register  to view this content.

to

Please Login or Register  to view this content.

Putting it back as solved now.

Have a good Sunday everyone.

Although I did put this as solved, I am noticing that it is very slow when copying large amounts of data.

That's A:AV with roughly 1500 rows.

Are there any ways to speed up this process or will things simply be slow when the dataset is as large as this...?

I replaced Sheets("Data") with ActiveWorkbook.ActiveSheet in order to use the macro on all desired sheets without having to name the sheet.

I wouldn't imagine that to make a difference.

Thanks in advance.

How does the new macro compare to your original macro? is it faster or slower?

Copy pasting only visible rows can become slow because your copy range is split up into a new copy and paste range every time a row is hidden so if every 3 rows 1 row is hidden
copy paste will do 1500/4 = 325 copy and paste actions. that makes it slow.


If possible sorting the data so there is one continues range can help inprove copy/paste speed.

Wow, CK76!

That was amazing and made a HUGE difference. I could copy my entire unfiltered table in about 1 second or less. For filtered data (100-500 rows) in the blink of an eye.

Thanks again for all help! Really appreciated.

Have a nice day!

Best regards,

Elijah

Originally Posted by CK76

One way is to load range to variant array and write back to range. This is typically one of the fastest method.

Another is advanced filter, which will require criteria range set up, but will be faster than autofilter method.

These are two methods I typically use.

Ex: Using array and collection...

Please Login or Register  to view this content.

Hi, CK76,

This macro have been very useful to me.

Can it be modified to work between workbooks? Where Workbook 1 is a normal macro-activated workbook and Workbook 2 is a *.csv file?

Thanks very much in advance.

Elijah

If not, I will probably try to set up a dynamic link between sheet 2 (copied filtered data) and the *.csv file using PowerQuery.

I was able to handle this on my own, so no worries.