Solved

Sort Data

Posted on 2012-03-14
9
282 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 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
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!

 
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

Industry Leaders: 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

Suggested Solutions

Title # Comments Views Activity
Excel vba question 7 39
Delete row if does not start with 0 43 36
Script/Formula to move data to another cell 6 16
macro to compare 2 Excel columns. 14 22
Using Word 2013, I was experiencing some incredible lag when typing.  Here's what worked for me....
Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
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…

713 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