Sort Specific Rows without Sorting Entire Worksheet 1

[Rzrbkpk]

The following is a small scale of a data set I have in Excel:


Country State City Count Date
U.S. New York Albany 200 2/4/2013
U.S. New York Albany 200 1/6/2013
U.S. New York Albany 200 12/31/2012
U.S. New York Buffalo 10 4/10/2013
U.S. New York Buffalo 10 1/5/2013
U.S. Georgia Savannah 15 2/10/2013
U.S. Georgia Savannah 15 11/1/2012
U.S. Georgia Atlanta 500 12/5/2013

I would like to sort each state by date without changing the order the states are in. My customer has requested that the states be placed in a specific order that is not alpha or numeric.

I did attempt the following, but couldn't get it to work:

Sub Sort_States_byDate()

Dim dataWS As Worksheet
Dim rowLimit As Integer
Dim colLimit As Integer
Dim a As Range

Set dataWS = ThisWorkbook.Sheets("Active")
Worksheets("Active").Activate
    
rowLimit = dataWS.UsedRange.Rows.Count
colLimit = dataWS.UsedRange.Columns.Count
    
Set a = Worksheets("Active").Range(Cells(1, 1), Cells(rowLimit, colLimit))

    With Worksheets("Active")
        a.AutoFilter Field:=2, Criteria1:="New York"
            With Cells.SpecialCells(xlCellTypeVisible)
                .Sort Key1:=Range("E1"), order1:=xlAscending
            End With
    End With
End Sub

The autofilter functions properly, but I get the run-time error "The command you chose cannot be performed with multiple selections. Select a single range and click the command again."

I'm not determined to use this approach, so suggestions for alternatives would be gladly welcomed.

 

[SkipVought]

This can be accomplished without VBA, using a simple formula in a helper column and the Sort feature.

In column F...
[tt]
F2: =if(B2=B1,0,1)+F1
[/tt]
...and copy down.

This creates a sequential group number for each state value.

Sort on F then E.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[SkipVought]

result

Country State     City    Count Date	
U.S.    New York  Albany    200 12/31/2012  1
U.S.    New York  Buffalo    10  1/5/2013   1
U.S.    New York  Albany    200  1/6/2013   1
U.S.    New York  Albany    200  2/4/2013   1
U.S.    New York  Buffalo    10  4/10/2013  1
U.S.    Georgia   Savannah   15 11/1/2012   2
U.S.    Georgia   Savannah   15  2/10/2013  2
U.S.    Georgia   Atlanta   500 12/5/2013   2

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Rzrbkpk]

Skip, thanks again for your assistance. As indicated in my initial post, the data set is a small scale of a larger data set. I'd rather not have to take this manual step for each worksheet containing this data. This approach creates a manual process that has to be repeated multiple times a week. My thought is to write code once to do the job automatically so I don't have to do a process repeatedly.

 

[SkipVought]

So EVERY sheet has this data structure or a subset of sheets?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Rzrbkpk]

That's correct. Each worksheet has the exact same structure, but different data.

 

[SkipVought]

I'd suggest using the formula in a macro and sort the table.

Turn on your macro recorder and record 1) paste the formula in F2, 2) fill down, 3) sort on F & E, 4) delete column F.

Post back with your recorded code.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Rzrbkpk]

Thanks, that works. Looks like I was over-complicating it.

Sub Macro1()

    Range("F2").Select
    ActiveCell.FormulaR1C1 = "=IF(RC[-4]=R[-1]C[-4],0,1)+R[-1]C"
    Range("F2").Select
    Selection.AutoFill Destination:=Range("F2:F9"), Type:=xlFillDefault
    Range("F2:F9").Select
    ActiveWorkbook.Worksheets("Active").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Active").Sort.SortFields.Add Key:=Range("F2:F9"), _
        SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    ActiveWorkbook.Worksheets("Active").Sort.SortFields.Add Key:=Range("E2:E9"), _
        SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("Active").Sort
        .SetRange Range("A1:F9")
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    Columns("F:F").Select
    Selection.Delete Shift:=xlToLeft

Here are the results:

Country State     City    Count Date	
U.S.    New York  Albany    200 12/31/2012  
U.S.    New York  Buffalo    10  1/5/2013   
U.S.    New York  Albany    200  1/6/2013   
U.S.    New York  Albany    200  2/4/2013   
U.S.    New York  Buffalo    10  4/10/2013  
U.S.    Georgia   Savannah   15 11/1/2012   
U.S.    Georgia   Savannah   15  2/10/2013  
U.S.    Georgia   Atlanta   500 12/5/2013

 

[Rzrbkpk]

Just to feed my curious side, what was wrong with my initial code?

 

[SkipVought]

You might try this

Sub test()
    Dim ws As Worksheet, r As Range, sPrev As String, lRow1 As Long
    
    For Each ws In Worksheets
        With ws
            lRow1 = 2
            For Each r In .Range(.[b2], .[b2].End(xlDown))
                If sPrev <> r.Value And sPrev <> "" Then
                    With Intersect(.UsedRange, .Range(Cells(lRow1, 1), Cells(r.Row - 1, 1)).EntireRow)
                        .Select
                        .Sort Key1:=.Cells(1, "E"), order1:=xlAscending, _
                        Header:=xlNo
                    End With
                    lRow1 = r.Row
                End If
                sPrev = r.Value
            Next
            'last group
            With Intersect(.UsedRange, .Range(Cells(lRow1, 1), Cells(.UsedRange.Rows.Count, 1)).EntireRow)
                .Select
                .Sort Key1:=.Cells(1, "E"), order1:=xlAscending, _
                Header:=xlNo
            End With
        End With
    Next
End Sub

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[SkipVought]

The code I published is somewhat similar to yours. This cycles thru all sheets and sorts each group without the filter that your code has. I have Selects, which I don't usually use, to show you what range is being sorted.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Rzrbkpk]

Thanks again for help. Your created code works like a charm. I appreciate your time in doing that, as it helps me to learn. The other solution, as rudimentary as it appears, is also effective and is easier to understand for lower level users such as myself.