VBA如果值小于指定值,则删除整行 - 删除错误的行 [英] VBA Delete entire row if value is less than specified value - deletes wrong rows
本文介绍了VBA如果值小于指定值,则删除整行 - 删除错误的行的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我正在尝试运行一个搜索工作表列S的小宏,如果列S中的值<0.501,则会删除该行。
我现在运行的代码删除一些行,但是看起来是随机的,而不是基于s中的单元格值。任何人都可以看到我的错误来自哪里?
Sub sort_delete_500cust()
Dim WS_Count As Integer
Dim I,K As Integer
Dim endrow As Long
'将WS_Count设置为等于活动
'工作簿中的工作表数。
WS_Count =工作簿(Standard.xlsx)。Worksheets.count
'开始循环。
对于I = 1到WS_Count
与工作表(I)
endrow = .Range(a& .Rows.count).End(xlUp ).row'仅在单元格未被加载时有效
范围(A2:v2& endrow).Sort _
Key1:= Range(s2),Order1:= xlDescending'按列分类
对于K = 2 To endrow
如果.Cells(K,19).Value< 0.501然后
.Range(S& K).EntireRow.Delete
如果
下一个K
结束于
下一个我
结束子
干杯
解决方案
您需要以相反的顺序遍历您的 K
循环。否则,删除行将被删除,因为它们被删除操作向上移动,并且您的 K
值递增。
对于K = endrow到2步骤-1
如果CDec(.Cells(K,19).Value)< pre>
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
这篇关于VBA如果值小于指定值,则删除整行 - 删除错误的行的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文