Date sorting on a worksheet via VBA code

[Rabbitoh]

When I use macro code to sort a list of row data that includes blank (empty) rows (because the rows with data will always vary) based on dates (dd/mm/yy in column B5) in Ascending order, 870 of the blank rows (of the 10000 allowed for) gets placed above my data rows (of which there are only around 200 rows). My dates do however get sorted in oldest date first (which is what I want).

Example:

Range("A5:G10000").Select
Range("G10000").Activate
Selection.Sort Key1:=Range("B5"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortTextAsNumbers
Range("A5").Select

If I change the code to resort the data in Decending order, the blank rows do get pushed to the bottom. However, my dates get reversed (latest first) which is not what I want.

I do not get this problem if I perform an Ascending order sort manually and do not understand why doing it with macro code would make any difference - but it does.

How can I get my macro to:

locate the first (A5) and last (varies) row of data in the list (without including column headings), then

select the range of columns that I require to have included (A through G), then

sort the data in ascending order based on the dates in column B

???

 

[HaHoBe]

Hi, Rabbitoh,

relate on a column where data is stored for every record (I assumed this to be Column A):

With Range("A5:G" & Cells(Rows.Count, "A").End(xlUp).Row)
    .Sort Key1:=Range("B5"), _
    Order1:=xlAscending, _
    Header:=xlNo, _
    OrderCustom:=1, _
    MatchCase:=False, _
    Orientation:=xlTopToBottom, _
    DataOption1:=xlSortTextAsNumbers
End With

As Dates are numeric values starting from 1/1/1900 with 1 sorting Descending puts the largest number (the latest date) on top. To solve the problem (I couldn´t figure it out using Excel97) use 2 sorts: one to put the empty rows to the end, one to then decide the range to sort on Column B.

Ciao,
Holger

 

[Rabbitoh]

Excellent, worked perfectly. Thanks heaps.