Excel VBA-在某些条件下删除行 [英] Excel VBA - Delete Rows on certain conditions

问题描述

如果一行在 D列中的值为 INACTIVE ，在 H列中的值为#N/A ，我要删除该行.

If a row has the value INACTIVE in column D and #N/A in column H, I want to delete that row.

我尝试通过下面的代码实现这一点，但实际上没有行被删除.

I tried to achive that with my code below, but no row actually gets deleted.

Dim ws3 As Worksheet
Dim r As Integer
Set ws3 = ThisWorkbook.Sheets("Sheet2")
With ws3
For r = Sheet2.UsedRange.Rows.Count To 2 Step -1
If Cells(r, "D").Value = "INACTIVE" And Cells(r, "H").Value = "#N/A" Then
Sheet2.Rows(r).EntireRow.Delete
End If
Next
End With

推荐答案

几个问题.

• 您没有正确限定范围对象.
  • 您(正确地)将与ws3一起使用，但再也不要参考它了
  • You don't properly qualify your range objects.
    • You (properly) use With ws3 but then never refer back to it
    • Integer 限制为32768，并且工作表中可以有更多行.
    • 无论如何，VBA都会在内部将Integer转换为Long.
    • Integer is limited to 32768 and there could be many more rows in a worksheet.
    • VBA will convert Integer to Long internally anyway.

    对您的代码进行以下修改可能会起作用:

    The following modification of your code may work:

    Option Explicit
    Sub due()
      Dim ws3 As Worksheet
      Dim r As Long
      Dim lastRow As Long
    
    Set ws3 = ThisWorkbook.Sheets("Sheet2")
    With ws3
        lastRow = .Cells(.Rows.Count, "D").End(xlUp).Row
        For r = lastRow To 2 Step -1
            If .Cells(r, "D").Value = "INACTIVE" And .Cells(r, "H").Text = "#N/A" Then
                .Rows(r).EntireRow.Delete
            End If
        Next
    End With
    
    End Sub
    

    这篇关于Excel VBA-在某些条件下删除行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！