How to sort data with macro or VBA

Hi bmmerkx
Thanks for the reply,
I can record a macro very basic ones, i can create based on todays data, but when i get new file tomorrow, i paste row data into macro file and when i click run macro, it does get messey and doesnt pcik up the data i want.

Hello Benjamin2008,

I see the columns you want to keep. The rows look like column "E", Document Type, is used to determine what will be kept. Is this the complete list? Will this list change?

Hello Benajmin2008,

Sorry for the late response. This was a tricky problem. The biggest issue I had with writing the macro was execution speed. I tried many different ways and this version gave me the best performance. Add this macro to a separate VBA module in your workbook. I am still unable to upload files.

Please Login or Register  to view this content.

hi all,

Originally Posted by Leith Ross

... The biggest issue I had with writing the macro was execution speed. I tried many different ways and this version gave me the best performance...

Leith,
I'm curious, what were the different ways that you tried & how did they time/rank in terms of performance?

Rob

Hello Rob,

To make a long story short, I originally planned on clearing the data rows that didn't match, sort the rows, and use SpecialCells to delete the blanks. The big obstacle was the top 7 rows contain merged cells. Even with the range defined as only the data in the table, Excel would throw an error saying that merged cells must all be the same size. To get around that issue the data needed to be copied without those rows for sorting, filtering, etc. The largest amount of time is spent deleting the unneeded columns. This proved faster that assembling only the data for each row with only the cells of interest and copying them over.

The timing was measured by placing a a variable at the beginning of the Sub to hold the Timer value, and another at the end of the Sub to hold the timer value at that point. Subtracting the two variables gives me the total execution time in microseconds.

Hello Benjamin20008,

The speed issue was still bothering me and I worked on the macro some more. This version ran in 23 seconds on 12,000 lines of data. I used the data in your workbook and added some full lines and then replicated it to fill 12000 rows. Instead of deleting the columns, I hid them. This saved a lot of time. I can live with this speed. Here is the improved version...

Please Login or Register  to view this content.

hi Leith,

Ahhh, merged cells rear their ugly heads again!
Oh well, with your use of DocType you've bypassed the potential issue related to the limitation of specialcells (see Ron DeBruin's site) .

Rob