Dynamic Sort for defined, yet growing, data set

I am writing a macro for stock analysis.

I append new data to the bottom of my data table.

The data comes in this format (all data in one row, for each day):

3-Aug-10 1,125.34 1,125.44 1,116.76 1,120.46 4,071,820,000 1,120.46
2-Aug-10 1,107.53 1,127.30 1,107.53 1,125.86 4,144,180,000 1,125.86
30-Jul-10 1,098.44 1,106.44 1,088.01 1,101.60 4,006,450,000 1,101.60
29-Jul-10 1,108.07 1,115.90 1,092.82 1,101.53 4,612,420,000 1,101.53
28-Jul-10 1,112.84 1,114.66 1,103.11 1,106.13 4,002,390,000 1,106.13
27-Jul-10 1,117.36 1,120.95 1,109.78 1,113.84 4,725,690,000 1,113.84
26-Jul-10 1,102.89 1,115.01 1,101.30 1,115.01 4,009,650,000 1,115.01
23-Jul-10 1,092.17 1,103.73 1,087.88 1,102.66 4,524,570,000 1,102.66
22-Jul-10 1,072.14 1,097.50 1,072.14 1,093.67 4,826,900,000 1,093.67

Since I don’t always append data every day, I have set up my macro to only process the last 30 rows of data (instead of the whole data set of 15,000+ rows).

I want the newly appended data to be sorted oldest to newest based on date, ascending, like this ( (all data in one row, for each day), sorted prior to processing):

22-Jul-10 1,072.14 1,097.50 1,072.14 1,093.67 4,826,900,000 1,093.67
23-Jul-10 1,092.17 1,103.73 1,087.88 1,102.66 4,524,570,000 1,102.66
26-Jul-10 1,102.89 1,115.01 1,101.30 1,115.01 4,009,650,000 1,115.01
27-Jul-10 1,117.36 1,120.95 1,109.78 1,113.84 4,725,690,000 1,113.84
28-Jul-10 1,112.84 1,114.66 1,103.11 1,106.13 4,002,390,000 1,106.13
29-Jul-10 1,108.07 1,115.90 1,092.82 1,101.53 4,612,420,000 1,101.53
30-Jul-10 1,098.44 1,106.44 1,088.01 1,101.60 4,006,450,000 1,101.60
2-Aug-10 1,107.53 1,127.30 1,107.53 1,125.86 4,144,180,000 1,125.86
3-Aug-10 1,125.34 1,125.44 1,116.76 1,120.46 4,071,820,000 1,120.46

If I paste the data in and do a manual sort using the “sort button” in excel, then run my macro, everything works perfectly.

The cell in the top, leftmost portion is the range key (i.e. the "date", always in Column A) and the range is always 30 rows up from the bottom, and the range always has 6 columns (i.e. A:G).

I want to include code in my macro to sort the last 30 rows of data every time, prior to processing the data, so I don’t have to do it manually).

Since the data set is ever increasing, the sort key (i.e. Column A) row number keeps changing.

How do I dynamically sort the last 30 rows of columns A:G?

Thanks!

Assuming i have understood your requirements, call either macro depending if you want to sort last 30 (SortLast30) or all (SortAll).

Please Login or Register  to view this content.

Worked perfectly!

I added this code to my macro, and simply "called" it in the middle and continued the rest of my code to the end.

Great Solution!