更新时间:2023-02-06 23:09:30
您需要以相反的顺序遍历您的 K
循环。否则,删除行将被删除,因为它们被删除操作向上移动,并且您的 K
值递增。
CDec(0.501)然后
.Range(S& K).EntireRow.Delete
结束如果
下一个
I'm attempting to run a small macro that searches column S of a worksheet and if the value in column S is <0.501, it deletes that row.
The code I am running at the moment deletes some rows but appears to do so randomly rather than based on the cell value in s. Can anyone see where my error is coming from?
Sub sort_delete_500cust()
Dim WS_Count As Integer
Dim I, K As Integer
Dim endrow As Long
' Set WS_Count equal to the number of worksheets in the active
' workbook.
WS_Count = Workbooks("Standard.xlsx").Worksheets.count
' Begin the loop.
For I = 1 To WS_Count
With Worksheets(I)
endrow = .Range("a" & .Rows.count).End(xlUp).row ' only works if cells are unmerged
Range("A2:v2" & endrow).Sort _
Key1:=Range("s2"), Order1:=xlDescending 'key is the sort by column
For K = 2 To endrow
If .Cells(K, 19).Value < 0.501 Then
.Range("S" & K).EntireRow.Delete
End If
Next K
End With
Next I
End Sub
Cheers!
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 your K
value increments over them.
For K = endrow To 2 Step -1
If CDec(.Cells(K, 19).Value) < CDec(0.501) Then
.Range("S" & K).EntireRow.Delete
End If
Next