Sorting Grouped Rows

mombo

New Member
Joined
Sep 15, 2017
Messages
7
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]-->
Date Name Location Status
2017 - 04/13
John Boston active
David Chicago off
Paul Detroit active
Steven Dallas active
Richard Miami off
2017 - 02/15
Donald Boston off
Anthony Providence off
Walter Seattle active
Robert Dallas off
2017 - 08/26
Peter New York active
Charles Orlando active
Benjamin Sacramento off
Victor Hartford off
Jason DC off

<tbody>
</tbody>

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?
 

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.
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.
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.
 
Upvote 0
@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?
 
Last edited:
Upvote 0
@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?
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
This should work in Xl 97
Code:
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
 
Upvote 0
@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.
 
Upvote 0

Forum statistics

Threads
1,213,510
Messages
6,114,040
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top