如何使用VBA根据标准删除Excel中的行？ [英] How to delete rows in Excel based on criteria using VBA?

问题描述

我正在构建一个宏来格式化一张数据，并删除不适用的数据行。具体来说，我正在寻找删除列L =ABC的行以及删除列AA>DEF的行。

到目前为止，我已经能够实现第一个目标，而不是第二个目标。现有的代码是：

  Dim LastRow As Integer 
 Dim x，y，z As Integer 
 Dim StartRow，StopRow As Integer 
 
对于x = 0 To LastRow 
 If（Range（L1）。Offset（x，0）=ABC）然后
 Range L1）。Offset（x，0）.EntireRow.Delete 
x = x  -  1 
 
如果
  

解决方案

通常使用AutoFilter而不是循环范围更快[/ p>

以下代码创建一个工作列，然后使用公式检测删除条件，然后自动筛选并删除结果记录

工作列中提供公式

= OR（L1 =ABC，AA1DEF）
到第一个空白的行1列然后复制到远远的真正使用范围​​。然后用AutoFilter快速删除任何TRUE记录

  Sub QuickKill（）
 Dim rng1 As Range，rng2 As Range， rng3 As Range 
设置rng1 = Cells.Find（*，，xlValues，xlByColumns，xlPrevious）
设置rng2 = Cells.Find（*，，xlValues，xlByRows，xlPrevious）
设置rng3 =范围（单元格（rng2.Row，rng1.Column），单元格（1，rng1.Column））
 Application.ScreenUpdating = False 
行（1）.Insert 
 with rng3.Offset（-1，1）.Resize（rng3.Rows.Count + 1，1）
 .FormulaR1C1 == OR（RC12 =ABC，RC27DEF ）
 .AutoFilter字段：= 1，Criteria1：=TRUE
 .EntireRow.Delete 
 On Error Resume Next 
'，以防所有行已被删除
 .EntireColumn.Delete 
错误GoTo 0 
结束
 Application.ScreenUpdating = True 
 End Sub 
  

I am currently building a macro to format a sheet of data as well as to remove inapplicable rows of data. Specifically, I am looking to delete rows where Column L = "ABC" as well as delete rows where Column AA <> "DEF".

So far I have been able to achieve the first objective, but not the second. The existing code is:

Dim LastRow As Integer
Dim x, y, z As Integer
Dim StartRow, StopRow As Integer

For x = 0 To LastRow
    If (Range("L1").Offset(x, 0) = "ABC") Then
    Range("L1").Offset(x, 0).EntireRow.Delete
    x = x - 1

End If

解决方案

It is normally much quicker to use AutoFilter rather than loop Ranges

The code below creates a working column, then use a formula to detect delete criteria and then autofilter and delete the result records

The working column puts a formula

=OR(L1="ABC",AA1<>"DEF") into row 1 of the first blank column then copies down as far ar the true used range. Then any TRUE records are quicklly deleted with AutoFilter

Sub QuickKill()
    Dim rng1 As Range, rng2 As Range, rng3 As Range
    Set rng1 = Cells.Find("*", , xlValues, , xlByColumns, xlPrevious)
    Set rng2 = Cells.Find("*", , xlValues, , xlByRows, xlPrevious)
    Set rng3 = Range(Cells(rng2.Row, rng1.Column), Cells(1, rng1.Column))
    Application.ScreenUpdating = False
    Rows(1).Insert
    With rng3.Offset(-1, 1).Resize(rng3.Rows.Count + 1, 1)
        .FormulaR1C1 = "=OR(RC12=""ABC"",RC27<>""DEF"")"
        .AutoFilter Field:=1, Criteria1:="TRUE"
        .EntireRow.Delete
        On Error Resume Next
        'in case all rows have been deleted
        .EntireColumn.Delete
        On Error GoTo 0
    End With
    Application.ScreenUpdating = True
End Sub

这篇关于如何使用VBA根据标准删除Excel中的行？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！