Sorting table on date column

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Keenly52 - 30 Mar 2005 03:38 GMT

I have created a macro to sort a table within a protected document. The sort
works fine except on the date column.

All cells in the date column are prepopulated with a date field set to MMMM
d, yyyy; some have dates in them, some are left empty. The sort works fine
except ...

The problem: when sorting "ascending" it sees the "empty" fields as the
earliest dates and places those rows at the top of the table.

Other than placing a fictitioius future date in the empty fields, is there
any way for them to be sorted to the bottom?

Thanks very much for any assistance.

Cheers. Ken.

Reply to this Message

Greg - 30 Mar 2005 14:21 GMT

Ken,

Well when Word sorts it puts nothing before anything else. You will
then have to evaluate the column for empty cells between the heading
row and the first date.

Count them then grab the first date and put it in the first empty cell.

Sub ScratchMacro()
Dim oRng As Range
Dim oCell As Cell
Dim oCol As Column
Dim i As Long, j As Long

'Count the empty cells at the top
If Not Selection.Information(wdWithInTable) Then Exit Sub
Set oCol = Selection.Columns(1)
For i = 1 To oCol.Cells.Count - 1 'Use i = 0 for if no header row
If Len(oCol.Cells(i + 1).Range) > 2 Then Exit For
Next i
If i = 0 Then Exit Sub
For j = i + 1 To oCol.Cells.Count
Set oRng = oCol.Cells(j).Range
oRng.MoveEnd wdCharacter, -1
'Use (j -i) if no header row
oCol.Cells(j - (i - 1)).Range.FormattedText = oRng.FormattedText
If j > oCol.Cells.Count - i Then oRng.Delete
Next j
End Sub

Reply to this Message

Keenly52 - 30 Mar 2005 18:39 GMT

Nuttin' before somthin' - seems bass ackwards to me.

Thanks very much for this - haven't tried it yet - when it works you will
have saved me a lot of effort - I literally could not have figured this out
if I spent all day.

Thanks Greg and cheers.
Ken.

> Ken,
>
[quoted text clipped - 25 lines]
> Next j
> End Sub

Reply to this Message