有效的删除行的方法（多重标准）VBA [英] Efficient way to delete rows (Multiple criterias) VBA

问题描述

我有一张表格，在Col A里有一个字符串A，而Col B由字符串B组成。

我想用Word'Begründung'我在使用从这些来源找到的以下代码如果单元格不包含'@'，则可以有效地删除整行... & 基于搜索键VBA删除行

  Sub KeepOnlyAtSymbolRows（）
 Dim ws As Worksheet 
 Dim rng As Range 
 Dim lastRow As Long 
 
设置ws = ActiveWorkbook.Sheets（Sheet1）
 
 lastRow = ws.Range（A& ws.Rows.Count）.End（xlUp）.Row 
 
设置rng = ws.Range（A1：A& lastRow）
 
'过滤并删除除标题行以外的所有行
带有rng 
 .AutoFilter字段： = 1，Criteria1：=< *Begründung*
 .Offset（1，0）.SpecialCells（xlCellTypeVisible）.EntireRow.Delete 
 End with 
 
'关闭过滤器
 ws.AutoFilterMode = False 
 End Sub 
  

我想为Col B添加另一个标准。无论何时在Col BNein中遇到该行被保留，其余的被删除，但同时保留在Col A中的Begrüundung其余的被删除。换句话说，在表格中遇到单词Begründung和Nein的情况下，这些行被保留，其余的被删除。

如果有人能帮忙，我真的很感激。

解决方案

p>尝试这样：

  Sub DeleteWithMultipleColumnsCriterias（）
 Dim ws As Worksheet 
 Dim rng As Range 
 Dim lastRow As Long 
 
设置ws = ActiveWorkbook.Sheets（Sheet1）
 
 lastRow = ws.Range（A& ws.Rows.Count ）.End（xlUp）.Row 
 
设置rng = ws.Range（A1：B& lastRow）
 
'过滤并删除除标题行
带有rng 
 .AutoFilter字段：= 1，Criteria1：=<> *Begründung*
 .AutoFilter字段：= 2，Criteria1：=< * * Nein * 
 .Offset（1，0）.SpecialCells（xlCellTypeVisible）.EntireRow.Delete 
结束
 
'关闭过滤器
 ws.AutoFilterMode = False 
 End Sub 
  

I have a sheet where in Col A there is a String A and Col B consists of String B.

I want to keep rows with the Word 'Begründung' in Col A and 'Nein' in Col B.

I am using the following code found from these sources Efficient way to delete entire row if cell doesn't contain '@' & Delete Row based on Search Key VBA

Sub KeepOnlyAtSymbolRows()
    Dim ws As Worksheet
    Dim rng As Range
    Dim lastRow As Long

    Set ws = ActiveWorkbook.Sheets("Sheet1")

    lastRow = ws.Range("A" & ws.Rows.Count).End(xlUp).Row

    Set rng = ws.Range("A1:A" & lastRow)

    ' filter and delete all but header row
    With rng
        .AutoFilter Field:=1, Criteria1:="<>*Begründung*"
        .Offset(1, 0).SpecialCells(xlCellTypeVisible).EntireRow.Delete
    End With

    ' turn off the filters
    ws.AutoFilterMode = False
End Sub

I want to add another criteria for the Col B. Wherever in Col B 'Nein' is encountered that row is kept and the rest is deleted but at the same time 'Begrüundung in Col A is kept and the rest is deleted.

In other words wherever the words 'Begründung' and 'Nein' is encountered in the sheet those rows are kept and the rest is deleted.

I would really be grateful if any of you could help.

解决方案

Try this:

Sub DeleteWithMultipleColumnsCriterias()
    Dim ws As Worksheet
    Dim rng As Range
    Dim lastRow As Long

    Set ws = ActiveWorkbook.Sheets("Sheet1")

    lastRow = ws.Range("A" & ws.Rows.Count).End(xlUp).Row

    Set rng = ws.Range("A1:B" & lastRow)

    ' filter and delete all but header row
    With rng
        .AutoFilter Field:=1, Criteria1:="<>*Begründung*"
        .AutoFilter Field:=2, Criteria1:="<>*Nein*"
        .Offset(1, 0).SpecialCells(xlCellTypeVisible).EntireRow.Delete
    End With

    ' turn off the filters
    ws.AutoFilterMode = False
End Sub

这篇关于有效的删除行的方法（多重标准）VBA的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！