Sorting Grouped Rows

[mombo]

Using Excel 97, I made a spreadsheet that has groups of names and other information. Each group is preceded by a single row in the group having a date.

This is an example of the organization:

<!--[if gte mso 9]><xml> <w:WordDocument> <w:View>Normal</w:View> <w:Zoom>0</w:Zoom> <w:Compatibility> <w:BreakWrappedTables/> <w:SnapToGridInCell/> <w:WrapTextWithPunct/> <w:UseAsianBreakRules/> </w:Compatibility> <w:BrowserLevel>MicrosoftInternetExplorer4</w:BrowserLevel> </w:WordDocument> </xml><![endif]--> [TABLE="class: MsoNormalTable"]
<tbody>[TR]
[TD] Date[/TD]
[TD] Name[/TD]
[TD] Location[/TD]
[TD] Status[/TD]
[/TR]
[TR]
[TD]

2017 - 04/13​

[/TD]
[TD] John[/TD]
[TD] Boston[/TD]
[TD] active[/TD]
[/TR]
[TR]
[TD][/TD]
[TD] David[/TD]
[TD] Chicago[/TD]
[TD] off[/TD]
[/TR]
[TR]
[TD][/TD]
[TD] Paul[/TD]
[TD] Detroit[/TD]
[TD] active[/TD]
[/TR]
[TR]
[TD][/TD]
[TD] Steven[/TD]
[TD] Dallas[/TD]
[TD] active[/TD]
[/TR]
[TR]
[TD][/TD]
[TD] Richard[/TD]
[TD] Miami[/TD]
[TD] off[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

2017 - 02/15​

[/TD]
[TD] Donald[/TD]
[TD] Boston[/TD]
[TD] off[/TD]
[/TR]
[TR]
[TD][/TD]
[TD] Anthony[/TD]
[TD] Providence[/TD]
[TD] off[/TD]
[/TR]
[TR]
[TD][/TD]
[TD] Walter[/TD]
[TD] Seattle[/TD]
[TD] active[/TD]
[/TR]
[TR]
[TD][/TD]
[TD] Robert[/TD]
[TD] Dallas[/TD]
[TD] off[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

2017 - 08/26​

[/TD]
[TD] Peter[/TD]
[TD] New York[/TD]
[TD] active[/TD]
[/TR]
[TR]
[TD][/TD]
[TD] Charles[/TD]
[TD] Orlando[/TD]
[TD] active[/TD]
[/TR]
[TR]
[TD][/TD]
[TD] Benjamin[/TD]
[TD] Sacramento[/TD]
[TD] off[/TD]
[/TR]
[TR]
[TD][/TD]
[TD] Victor[/TD]
[TD] Hartford[/TD]
[TD] off[/TD]
[/TR]
[TR]
[TD][/TD]
[TD] Jason[/TD]
[TD] DC[/TD]
[TD] off[/TD]
[/TR]
</tbody>[/TABLE]

The dates are entered as a string of numbers, and the cells are formatted to display as shown.

How can I sort the groups by date?

 

[BrianJN1]

This is not ideal!

I developed a macro which is very specific to your table using the Developer Macro Recorder.
It takes each specific date and copies it down against the other data belonging to that date.
Those copy/pasted dates are then 'hidden'.
When the sort is applied the illusion is that each date block has been separately selected and sorted.
That also means that the blank row between date blocks is also lost.

You can follow the development of this by reading the comments within the code.

Sub Blank_Date_sort()
'
' Blank_Date_sort Macro
'

'
    'Select first date
    Range("A2").Select
    Selection.Copy
   [I][B] 'Copy date down[/B][/I]
    Range("A3:A6").Select
    ActiveSheet.Paste
   [I] [B] 'Select next date[/B][/I]
    Range("A8").Select
    Selection.Copy
    [I][B]'Copy date down[/B][/I]
    Range("A9:A11").Select
    ActiveSheet.Paste
    [I][B]'Select next date[/B][/I]
    Range("A13").Select
    Selection.Copy
     [I][B]'Copy date down[/B][/I]
    Range("A14:A17").Select
    ActiveSheet.Paste
    [I][B]'Set range to turn off font colour[/B][/I]
    Range("[I]A3:A6,A9:A11,A14:A17[/I]").Select
    Range("A14").Activate
    Selection.Copy
    With Selection.Font
        .ThemeColor = [B][I]xlThemeColorDark1[/I][/B]
        .TintAndShade = 0
    End With
  [I][B]  'Begin and do sort by date[/B][/I]
    Columns("A:D").Select
    ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add Key:=Range("A2:A17") _
        , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("Sheet1").Sort
        .SetRange Range("A1:D17")
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
End Sub

Now, if you are basically comfortable with how that code worked then advise and then either myself or some other can attempt a modification which makes that code applicable to any listing that is structured as you offered; yeah, I reckon a blank row should be possible if needed.

 

[Fluff]

@BrianJN1
The OP is using Xl97, which does not support the modern colour formats you've used. Nor does it support the type of sorting you've used.
@mombo
you said

The dates are entered as a string of numbers, and the cells are formatted to display as shown.

What are the string of numbers?

 

[mombo]

@BrianJN1
The OP is using Xl97, which does not support the modern colour formats you've used. Nor does it support the type of sorting you've used.
@mombo
you said What are the string of numbers?

I simply enter the four-digit year immediately followed by the two-digit month and two-digit day. The column is formatted to separate the year from the month and day by a " - " and the month and day are separated by a slash.

I enter "20170413"
and the formatting changes it to "2017 - 04/13"

Does it matter how I do that, and if so, should I be doing something different?

 

[mombo]

Thank you for the code, Brian. I haven't tried it yet, but if it needs tweaking, I'll consult my daughter, who's a web application developer. I'm sure she'll be able to help me make it work for me.

 

[Fluff]

It doesn't particularly matter how you do it. It's just a matter of knowing the cell values, for sorting purposes.
I'll have a look at this later. I'm currently running something on Xl, that will take a long time to complete.

 

[mombo]

Okay. No rush. Thanks for your help.

 

[Fluff]

This should work in Xl 97

Sub SortGroupedRows()

    With Range("A2:A" & Range("B" & Rows.Count).End(xlUp).Row).SpecialCells(xlBlanks)
        .FormulaR1C1 = "=r[-1]c"
        .Font.Color = vbWhite
    End With
    
    With Range("A2:A" & Range("B" & Rows.Count).End(xlUp).Row)
        .Value = .Value
    End With

    With Range("A" & Rows.Count).End(xlUp).Offset(1)
        .Value = .Offset(-1).Value
        .Font.Color = vbWhite
    End With
    
    Range("A1:D" & Range("A" & Rows.Count).End(xlUp).Row).Sort Key1:=Range("A1"), _
        Order1:=xlDescending, Header:=xlYes, OrderCustom:=1, MatchCase:=False, _
        Orientation:=xlTopToBottom, DataOption1:=xlSortNormal
    
    With Application.FindFormat
        .Clear
        .Font.Color = vbWhite
    End With
    Columns(1).Replace What:="", Replacement:="", SearchFormat:=True, ReplaceFormat:=False
    Application.FindFormat.Clear
    
End Sub

 

[BrianJN1]

@mombo, (#5) that code certainly would have needed tweaking even it hadn't failed the color parameters as pointed out by Fluff.
My code merely looked at three specific blocks of data. My intent would have been to condense my code to something more general but still would not have been as efficient as Fluff has done.