Thread: Sorting within Boxes (revisited)

I need to revisit a closed thread (http://www.excelforum.com/excel-prog...hin-boxes.html).

RomperStomper gave me a great macro that I thought did what I needed, but it doesn't quite do it. See the attached file. On lines 148 and 149 of each tab you can see the issue. In the Unsorted, the correct folks report to the correct PACs. In the Sorted, different folks are reporting to the PACs.

Here's the macro Romper gave, which would be applied to the "Unsorted" tab to sort it correctly. I don't understand the macro well enough to try my hand at altering it, so hope someone older and wiser (or at least wiser) would take a look. Thanks in advance.

Sub Resort()
   Dim lngStartRow As Long, lngEndRow As Long, lngLastRow As Long
   Dim lngColCount As Long
   Dim rngSort
   
   lngLastRow = Cells(Rows.Count, 1).End(xlUp).Row
   lngColCount = Cells(1, Columns.Count).End(xlToLeft).Column
   
   lngStartRow = 2
   Do
      lngEndRow = Cells(lngStartRow, 1).End(xlDown).Row - 1
      Range(Cells(lngStartRow, "C"), Cells(lngEndRow, lngColCount)).Sort key1:=Cells(lngStartRow, "K"), _
                  order1:=xlAscending, key2:=Cells(lngStartRow, "I"), order2:=xlAscending, header:=xlNo
      lngStartRow = lngEndRow + 2
   Loop While lngEndRow < lngLastRow
End Sub

Maybe?

I just added a check in the script, it appears a couple lines up from the problem you really had nothing to sort ,because it was only one line?

Sub Resort()
   Dim lngStartRow As Long, lngEndRow As Long, lngLastRow As Long
   Dim lngColCount As Long
   Dim rngSort
 
   lngLastRow = Cells(Rows.Count, 1).End(xlUp).Row
   lngColCount = Cells(1, Columns.Count).End(xlToLeft).Column
 
   lngStartRow = 2
   Do
      lngEndRow = Cells(lngStartRow, 1).End(xlDown).Row - 1
 
      If lngEndRow - lngStartRow = 1 Then
      Else
 
      Range(Cells(lngStartRow, "C"), Cells(lngEndRow, lngColCount)).Sort key1:=Cells(lngStartRow, "K"), _
                 order1:=xlAscending, key2:=Cells(lngStartRow, "I"), order2:=xlAscending, header:=xlNo
 
      End If
 
      lngStartRow = lngEndRow + 2
   Loop While lngEndRow < lngLastRow
End Sub

...

That may not be the issue after all. I see your talking about PAC in your post. The pac are in col B the sort in not sorting col B.This is the first instance of 2 pacs in a col which appears to be the problem?

So maybe you just need to include col B in the sort?

Sub Resort()
   Dim lngStartRow As Long, lngEndRow As Long, lngLastRow As Long
   Dim lngColCount As Long
   Dim rngSort
 
   lngLastRow = Cells(Rows.Count, 1).End(xlUp).Row
   lngColCount = Cells(1, Columns.Count).End(xlToLeft).Column
 
   lngStartRow = 2
   Do
      lngEndRow = Cells(lngStartRow, 1).End(xlDown).Row - 1
 
      If lngEndRow - lngStartRow = 1 Then
      Else
 
      Range(Cells(lngStartRow, "B"), Cells(lngEndRow, lngColCount)).Sort key1:=Cells(lngStartRow, "K"), _
                 order1:=xlAscending, key2:=Cells(lngStartRow, "I"), order2:=xlAscending, header:=xlNo
 
      End If
 
      lngStartRow = lngEndRow + 2
   Loop While lngEndRow < lngLastRow
End Sub

Try This?

It looks like the problem may be with more than 2 numbers in range B, We may need to add a case or more if statments based of the mycount number. However see if this is any closer...

Sub Resort()
   Dim lngStartRow As Long, lngEndRow As Long, lngLastRow As Long
   Dim lngColCount As Long
   Dim rngSort
 
   lngLastRow = Cells(Rows.Count, 1).End(xlUp).Row
   lngColCount = Cells(1, Columns.Count).End(xlToLeft).Column
 
   lngStartRow = 2
   Do
      lngEndRow = Cells(lngStartRow, 1).End(xlDown).Row - 1
     MyCount = WorksheetFunction.CountA(Range(Cells(lngStartRow, "B"), Cells(lngEndRow, "B")))
 
     If MyCount > 1 Then
        If lngEndRow - lngStartRow = 1 Then
        Else
            Range(Cells(lngStartRow + 1, "C"), Cells(lngEndRow, lngColCount)).Sort key1:=Cells(lngStartRow, "K"), _
                 order1:=xlAscending, key2:=Cells(lngStartRow, "I"), order2:=xlAscending, header:=xlNo
        End If
     Else
        If lngEndRow - lngStartRow = 1 Then
        Else
            Range(Cells(lngStartRow, "C"), Cells(lngEndRow, lngColCount)).Sort key1:=Cells(lngStartRow, "K"), _
                 order1:=xlAscending, key2:=Cells(lngStartRow, "I"), order2:=xlAscending, header:=xlNo
        End If
     End If
      lngStartRow = lngEndRow + 2
   Loop While lngEndRow < lngLastRow
End Sub
 
...