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

Option Explicit
Sub SortDataSet(SortAll As Boolean)
     ' sort rows by column A either for all or last 30 records
     Dim LastRow As Long
     Dim RngSort As Range
     Dim All As Boolean
 
     On Error GoTo Failed
     With ActiveSheet
         ' get last row
         LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
          ' exit if no more than 1 data row
         If LastRow < 3 Then Exit Sub
         ' less than 30 data rows or do all
         If LastRow < 32 Or SortAll = True Then
             Set RngSort = .Range(Cells(2, "A"), Cells(LastRow, "G")).EntireRow
         Else
             Set RngSort = .Range(Cells(LastRow - 30, "A"), Cells(LastRow, "G")).EntireRow
         End If
         With Application
             .ScreenUpdating = False
             .EnableEvents = False
         End With
         RngSort.Sort key1:=.Cells(1, 1), order1:=xlAscending, Header:=xlNo
     End With
Failed:
     ActiveWindow.ScrollRow = 1
     With Application
         .ScreenUpdating = True
         .EnableEvents = True
     End With
     Set RngSort = Nothing
End Sub

Sub SortAll()
    ' sort all records
    SortDataSet (True)
End Sub
Sub SortLast30()
' sort last 30 records
    SortDataSet (False)
End Sub

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!