基于搜索键VBA删除行 [英] Delete Row based on Search Key VBA

问题描述

我试图删除使用VBA在列B中找到值X的每个行。但是，我有三个问题：

I am trying to delete every row in which the value "X" is found in column B using VBA. However, I'm having three problems:

1. 我无法让我的VBA代码从活动单元下移到下一个单元格（B3）使用cells.find方法（见下面的代码）


2. 我的代码不会删除列B中的值X的整个行


3. 列B中的数据量可以有所不同：它可以在今天的B10或明天的B100中结束（见下面的屏幕截图）

任何帮助将不胜感激。

Sub RemoveRows()   
    Dim strLookFor As String
    Dim strRow As String

    Worksheets("Sheet1").Range("B2").Activate

    strLookFor = "X"
    strRow = Range("B2").Address

    Do Until ActiveCell.Value = ""        
        MsgBox (ActiveCell.Value)        
        If ActiveCell.Value = strLookFor Then
            Rows.Delete (strRow)
        End If            
        strRow = Cells.Find(what:=strLookFor).Address
    Loop

    MsgBox ("Deleted all rows with value " & strLookFor)    
End Sub

推荐答案

使用 AutoFilter 比范围循环更有效率

Using an AutoFilter is much more efficient than a range loop

Sub QuickCull()
Dim ws As Worksheet
Dim rng1 As Range
Set ws = Sheets("Sheet1")
Set rng1 = ws.Range(ws.[b2], ws.Cells(Rows.Count, "B").End(xlUp))
Application.ScreenUpdating = False
With ActiveSheet
        .AutoFilterMode = False
        rng1.AutoFilter Field:=1, Criteria1:="X"
        rng1.Offset(1, 0).EntireRow.Delete
        .AutoFilterMode = False
    End With
Application.ScreenUpdating = True
End Sub

这篇关于基于搜索键VBA删除行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！