Solved

Sort Data

Posted on 2012-03-14
9
278 Views
Last Modified: 2012-03-25
I need Experts advice. Can we sort the data at Data sheet (attached the script) at the same time maintaining the row group (+)?  

Sub SortingAtoR()
Intersect(Range("A1").CurrentRegion, Range("A:R")).Sort _
    Key1:=Range("A2"), Order1:=xlAscending, Key2:=Range("B2"), Order2:=xlAscending, Header:=xlYes, _
    OrderCustom:=1, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal, DataOption2:=xlSortTextAsNumbers
End Sub

Open in new window

SortData.xls
0
Comment
Question by:Billa7
  • 4
  • 3
9 Comments
 
LVL 4

Expert Comment

by:SmittyPro
ID: 37725476
You can record a macro removing the grouping, sort, then reapply the grouping.
0
 
LVL 41

Expert Comment

by:dlmille
ID: 37727553
It really depends on how you are sorting and what your grouping logic is.

It appears to me from your last grouping workbook I worked on with you, that doing a date then title sort which appears to be what you're doing, you would invalidate the groups.

I tested with test data....

It appears that the sort you have doesn't even sort within groups when I change the data to test, but when I remove the groups, the sort works, correctly, but then the grouping is gone.

So, if you provide the grouping logic, we can remove the group, run the sort, then add the group back.

What is the grouping logic for your rows?

Dave
0
 

Author Comment

by:Billa7
ID: 37727567
Hi Dave,

I groping the date base on week (Monday to Sunday); I do realized by data without day reference. Are we still able to group the date without day reference? if yes, that will be great.
0
 
LVL 41

Expert Comment

by:dlmille
ID: 37727591
We can group on column A.  Is your definition of week from Sun to Saturday?
0
Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

 

Author Comment

by:Billa7
ID: 37727762
Hi Dave,

Yes, we can do that way.
0
 
LVL 41

Accepted Solution

by:
dlmille earned 500 total points
ID: 37727773
We can use the WEEKNUM function.  App creates helper column in column S on weeknum, then creates subtotals based on weeknum, then goes in and removes the subtotals giving the grouping back.

Sub SortingAtoR()
Dim lastRow As Long
Dim r As Range
Dim rDelete As Range

    On Error Resume Next
    Rows.RemoveSubtotal
    Rows.Ungroup
    On Error GoTo 0
    
    lastRow = Range("A" & Rows.Count).End(xlUp).Row
    Intersect(Range("A1").CurrentRegion, Range("A:R")).Sort _
        Key1:=Range("A2"), Order1:=xlAscending, Key2:=Range("B2"), Order2:=xlAscending, Header:=xlYes, _
        OrderCustom:=1, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal, DataOption2:=xlSortTextAsNumbers
        
    Range("S1").Value = "WeekNum"
    Range("S2:S" & lastRow).Formula = "=WEEKNUM($A2)"
    
    Range("A1:S" & lastRow).Subtotal GroupBy:=19, Function:=xlSum, TotalList:=Array(19), _
        Replace:=True, PageBreaks:=False, SummaryBelowData:=True
        
    'remove subTotals
    For Each r In Range("A:A").SpecialCells(xlCellTypeBlanks)
        If rDelete Is Nothing Then
            Set rDelete = r.Resize(, Range("S1").Column)
        Else
            Set rDelete = Union(r.Resize(, Range("S1").Column), rDelete)
        End If
    Next r
    
    rDelete.Delete shift:=xlUp
    Range("S:S").ClearContents
End Sub

Open in new window


See attached demonstration workbook.

Dave
SortData-r1.xls
0
 
LVL 41

Expert Comment

by:dlmille
ID: 37759471
Billa - are we done with this, ready for close out?

Dave
0
 

Author Closing Comment

by:Billa7
ID: 37762512
Thanks Dave.

Sorry for the delay.
0

Featured Post

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

PaperPort has a feature called the "Send To Bar". It provides a convenient, drag-and-drop interface for using other installed software, such as Microsoft Office. However, this article shows that the latest Office 2016 apps (installed with an Office …
Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now