Reply
 
Thread Tools Display Modes
  #1   Report Post  
Keenly52
 
Posts: n/a
Default 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   Report Post  
Greg
 
Posts: n/a
Default

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   Report Post  
Keenly52
 
Posts: n/a
Default

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

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
table column width problems Dena Tables 2 March 8th 05 04:51 AM
Table AutoFormats vs. Table Styles confusion Tony Jollans Tables 5 March 6th 05 08:18 PM
Dragging table column resets Sedonakids Tables 0 January 12th 05 02:11 AM
How do I adjust column width in a table? FruitIsBest Tables 1 December 4th 04 11:30 PM
using table style in 1st column Hutystng29 Tables 1 November 9th 04 03:31 PM


All times are GMT +1. The time now is 06:16 PM.

Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 Microsoft Office Word Forum - WordBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Word"