VBA排序decending不排序，不可预知的循环(VBA sort decending not s

我运行一个宏来删除一个工作簿格式，排序列S降序删除其中列的价值观是0.501之下行。 我得到了一些帮助，在这里修复代码的一部分

然而，我发现更多的问题。 该代码会出现非常难以预测。 按降序排序基于列S中的所有表的行不排序。 如果我改变Range ，以.Range代码中断。

           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

结束小组

任何见解，这些问题将不胜感激。

Answer 1:

该.Sort的代码行应该指的是Worksheet与您的合作。 所以，它应该使用.Range(...而不是仅仅Range(...)在你的情况下，它抛出一个错误，因为排序关键字也必须引用到工作表。

最终的代码看起来应该是这样的：

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

文章来源: VBA sort decending not sorting, unpredictable looping