Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Sorting table on date column
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. |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
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. "Greg" wrote: 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 |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
table column width problems | Tables | |||
Table AutoFormats vs. Table Styles confusion | Tables | |||
Dragging table column resets | Tables | |||
How do I adjust column width in a table? | Tables | |||
using table style in 1st column | Tables |