locked
Sorting Ascending within spceicified range RRS feed

  • Question

  • Hi Experts

     I've been trying different alternatives to sort the rows based on Col D per collection.

    This is the closest one, yet there are 2 bugs found.

    1- Loop and not able to exit when it reaches the last used rows.
    It keeps sorting until I press <Esc> to force quitting
    2- It's unable to sort where there is only one SKUs per collection
    It sort the next collection as well. Sometimes 3 collections sorted.
    e.g. Before Run - Row 9, 29, 32, 35, 45....

    Here is my code. What's wrong with my code?

    I am not able to insert images and link at this moment. 

    But I put the image URL down below.

    Sub SortingCollectionOnColD
    With ActiveSheet.Range("A:A")
        Set FindSubtotal = .Find("Subtotal", After:=.Range("A1"), LookIn:=xlValues)
            If Not FindSubtotal Is Nothing Then
                firstOne = FindSubtotal.Address
                Do
                    With FindSubtotal
                            Range("A" & FindSubtotal.Row - 1).Select
                            Set SortRange = Range(Selection, Selection.End(xlUp)).EntireRow
                            ActiveSheet.Sort.SortFields.Clear
                                ActiveSheet.Sort.SortFields.Add Key:=Range("C" & FindSubtotal.Row) _
                                    , SortOn:=xlSortOnValues, Order:=xlAscending
                                With ActiveSheet.Sort
                                    .SetRange SortRange
                                    .Header = xlNo
                                    .Orientation = xlTopToBottom
                                    .Apply
                                End With
                    End With
                    Set FindSubtotal = .FindNext(FindSubtotal)
                Loop While Not FindSubtotal Is Nothing And FindSubtotal.Address <> firstOne
            End If
    End With
    End Sub

    Fig1 - Before run

    https://social.msdn.microsoft.com/Forums/getfile/1546888

    Fig2-  Expected Result      

    https://social.msdn.microsoft.com/Forums/getfile/1546889

    Fig3- After run

    https://social.msdn.microsoft.com/Forums/getfile/1546890

    Monday, February 17, 2020 1:34 PM

All replies

  • To: kimfung20
    re:  loop won't stop

    Both conditions are not occurring together.
    The XL Find help file doesn't help.
    Try replacing the Loop line with two lines...

        If FindSubtotal is Nothing then Exit Do
    Loop Until FindSubtotal.Address = firstOne

    '---

    The free Excel workbook "Professional_Compare" is now available at MediaFire.
    (compares every cell Or each row against all rows (two worksheets) - choice of compare type - fast)
    Download (no ads) from:  http://www.mediafire.com/folder/lto3hbhyq0hcf/Documents
    Wednesday, February 19, 2020 3:23 AM