VBA sort decending not sorting, unpredictable looping

Question

I am running a macro to remove formatting from a workbook, sort column s descending delete rows where values in column s are under 0.501. I received some help to fix part of the code here

However, I have found additional problems. The code appears quite unpredictable. Sort descending based on column s does not sort the rows in all of the sheets. if I change Range to .Range the code breaks.

           Sub sort_delete_500cust()

     Dim WS_Count As Integer
     Dim i, K As Integer
     Dim endrow As Long
     Dim output_wb As Workbook

     ' Set WS_Count equal to the number of worksheets in the active
     ' workbook.
     Set output_wb = Workbooks("DMA_customers_5.xlsx")
        With output_wb

            WS_Count = output_wb.Worksheets.count

            ' Begin the loop.
            For i = 1 To WS_Count

                With output_wb.Worksheets(i)
                     '.Cells.ClearFormats
                    'MsgBox ActiveWorkbook.Worksheets(I).Name

                    endrow = .Range("a" & .Rows.count).End(xlUp).Row
                    'Worksheets(i).Cells.UnMerge

                                'key is the sort by column' only works if cells are unmerged
                    Range("A2:v" & endrow).Sort _
                    Key1:=Range("s2"), Order1:=xlDescending

                        For K = endrow To 2 Step -1

                            If CDec(.Cells(K, 19).Value) < 0.501 Then
                                'You need to traverse your K loop in reverse order. Otherwise rows will be skipped as you delete because they are shifted up by the delete operation and you K value increments over them.
                            .Range("S" & K).EntireRow.Delete
                            End If


                        Next K
                End With

            Next i

    End With

End Sub

Any insights into these problems would be much appreciated.

At first glance, this seems wrong: `Range("A2:v2" & endrow)`. Shouldn't it be `Range("A2:V" & endrow)`. Otherwise, it will take whatever the last row is, and add a 2 before it. (For example, if endrow is 50, instead of a Range from `A2:V50`, it becomes `A2:V250` — basodre, Mar 19 '15 at 12:47
Another comment. Do you intend to `Sort` each worksheet after all the necessary rows have been deleted? Currently, the code sorts the worksheet after each iteration through the inner loop (the loop that deletes the appropriate cell). — basodre, Mar 19 '15 at 12:51
@user3561813 Good point! I can move it out of the inner loop. — squar_o, Mar 19 '15 at 12:54
@user3561813, I've edited the code above as suggested. But it is currently only sorting one sheet in the workbook. — squar_o, Mar 19 '15 at 13:11

score 1 · Accepted Answer · answered Mar 19 '15 at 13:14

The .Sort line of code should refer to the Worksheet with which you are working. So, it should use .Range(... instead of just Range(...). In your case, it throws an error because the sort key must also refer to the worksheet.

Final code should look something like:

.Range("A2:v" & endrow).Sort _
Key1:=.Range("s2"), Order1:=xlDescending

VBA sort decending not sorting, unpredictable looping

1 Answers1

Linked