Solved

Sort Data

Posted on 2012-03-14
9
280 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
Gigs: Get Your Project Delivered by an Expert

Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

 
LVL 41

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 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

Live: Real-Time Solutions, Start Here

Receive instant 1:1 support from technology experts, using our real-time conversation and whiteboard interface. Your first 5 minutes are always free.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Excel User Form VBA Help 18 30
VBA name newly created sheet 4 20
Excel VBA get Access table names with ADO 2 20
VLOOKUP 6 16
My experience with Windows 10 over a one year period and suggestions for smooth operation
Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
Learn how to create and modify your own paragraph styles in Microsoft Word. This can be helpful when wanting to make consistently referenced styles throughout a document or template.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

813 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

19 Experts available now in Live!

Get 1:1 Help Now