代码不删除每个非数字行 [英] Code not deleting every non numeric row
问题描述
我这里有这段代码,它可以查看一列数字,用数字为单元格着色,如果是非数字条目(单元格用-"填充),则删除该单元格及其对应的行.它将删除一些非数字行,但不是全部.
I have this code here which looks through a column of numbers, colors the cells with numbers and deletes the cells and its corresponding rows if it's a nonnumeric entry(the cells are filled with "-"). It deletes some of the non numeric rows but not all of them.
Dim wbI As Workbook, wbO As Workbook
Dim wsI As Worksheet, wsO As Worksheet
Dim iCounter As Long
Dim iCounter1 As Long
Dim iCounter2 As Long
Dim lrow As Long, rw As Long
Dim OutLookApp As Object
Dim OutLookMailItem As Object
Dim MailDest As String
Dim subj As String
Dim bod As String
Dim lastrow As Long
Dim lastrow1 As Long
lastrow = wsI.Cells(Rows.Count, 4).End(xlUp).Row
lastrow1 = wsO.Cells(Rows.Count, 4).End(xlUp).Row
With wsO
For iCounter1 = 2 To lastrow
If wsO.Cells(iCounter1, 9) > 120 Then
wsO.Cells(iCounter1, 9).Interior.Color = RGB(255, 101, 101)
ElseIf wsO.Cells(iCounter1, 9) > 0 And wsO.Cells(iCounter1, 9) < 120 Then
wsO.Cells(iCounter1, 9).Interior.Color = RGB(169, 208, 142)
End If
Next iCounter1
With wsO
For iCounter2 = 2 To lastrow
If Not IsNumeric(Cells(iCounter2, 9)) Then
wsO.Cells(iCounter2, 9).EntireRow.Delete
End If
Next iCounter2
Rows("2:200").RowHeight = 30
End With
End With
推荐答案
您的代码似乎有两个问题.跳过一些应删除的行的主要问题可以通过从下到上循环来解决.无法以这种方式工作可能意味着删除一行后跳过该行,对行重新编号,然后增加到下一行.
There seem to be two problems with your code. The primary issue of skipping some rows that should be deleted can be cleared up by looping from the bottom to the top. Failing to work in ths manner may mean that a row is skipped after a row is deleted, the rows are renumbered and you increment on to the next row.
还有一个次要问题,您已在其中实现了使用...结束使用语句引用要作用的工作表,然后在引用单元格/范围/行时重申该工作表引用,或者将其完全丢弃.
There is a secondary issue where you have implemented a With ... End With statement that references the worksheet to be acted upon and then either reiterate the worksheet reference when referencing cells/ranges/rows or discard it altogether.
With wsO
For iCounter1 = 2 To LastRow
If .Cells(iCounter1, 9) > 120 Then
.Cells(iCounter1, 9).Interior.Color = RGB(255, 101, 101)
ElseIf .Cells(iCounter1, 9) > 0 And .Cells(iCounter1, 9) < 120 Then
.Cells(iCounter1, 9).Interior.Color = RGB(169, 208, 142)
End If
Next iCounter1
'this is the primary change
'loop from the bottom to the top when deleting rows
For iCounter2 = LastRow To 2 Step -1
If Not IsNumeric(.Cells(iCounter2, 9)) Then
.Cells(iCounter2, 9).EntireRow.Delete
End If
Next iCounter2
.Rows("2:200").RowHeight = 30
End With
请注意,Cells
变为.Cells
,Rows
变为.Rows
.句点(也称为.
或句点`)前缀将每个单元格/范围/行与在With ... End With块中引用的父工作表相关联.
Note that Cells
becomes .Cells
and Rows
become .Rows
. The period (aka .
or full stop`) prefix associates each cell/range/row with the parent worksheet referenced in the With ... End With block.
¹感谢 Scott Craner 注意到注释中从下到上的方法.
¹ Thanks to Scott Craner for noting the bottom-to-top method in comments.
这篇关于代码不删除每个非数字行的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!