Solved

Sort Data

Posted on 2012-03-14
9
283 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 42

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
Office 365 Training for Admins - 7 Day Trial

Learn how to provision tenants, synchronize on-premise Active Directory, implement Single Sign-On, customize Office deployment, and protect your organization with eDiscovery and DLP policies.  Only from Platform Scholar.

 
LVL 42

Expert Comment

by:dlmille
ID: 37727591
We can group on column A.  Is your definition of week from Sun to Saturday?
0
 

Author Comment

by:Billa7
ID: 37727762
Hi Dave,

Yes, we can do that way.
0
 
LVL 42

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 42

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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft Office Picture Manager is not included in Office 2013. This comes as a shock to users upgrading from earlier versions of Office, such as 2007 and 2010, where Picture Manager was included as a standard application. This article explains how…
I was prompted to write this article after the recent World-Wide Ransomware outbreak. For years now, System Administrators around the world have used the excuse of "Waiting a Bit" before applying Security Patch Updates. This type of reasoning to me …
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

752 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