VBA排序不排序，不可预测的循环 [英] VBA sort decending not sorting, unpredictable looping

问题描述

我正在运行一个宏以从工作簿中删除格式，对列中的值低于0.501排序列降序删除行。 我收到一些帮助修复部分代码

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

但是，我发现了其他问题。代码看起来很不可预测。根据列排序降序不排序所有工作表中的行。如果我将范围更改为 .Range 代码中断。

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.

推荐答案

.Sort 代码行应该参考您正在使用的工作表。所以，它应该使用 .Range（... 而不是 Range（...）。在你的情况下，它会抛出一个错误，因为排序键也必须引用工作表。

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排序不排序，不可预测的循环的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！