excel - 使用 InStr 删除列中不包含子字符串的所有行

Question

我想将我的表格减少到第二列中所有以“X”作为子字符串的行。为此，我写了以下内容：

Sub reduce()
   Dim lngRow As Long

    With ActiveWorkbook.ActiveSheet
        lngRow = .Cells(65536, 1).End(xlUp).Row

        Do
            pos = InStr(.Cells(lngRow, 2), "X")

            If Not pos > 0 Then

                .Rows(lngRow).Delete

            End If


            lngRow = lngRow - 1
        Loop Until lngRow = 1
    End With
End Sub

不幸的是，我收到了这个错误： https ://docs.microsoft.com/en-us/office/vba/Language/Reference/User-Interface-Help/application-defined-or-object-defined-error

标签： excelvba

Answer 1

如何在循环中执行此操作。它的代码更多，但比当前方法更有效。@BigBen 是正确的，在给定当前问题陈述的情况下，过滤器是最佳的

Sub Reduce()


'Declare variables
Dim ws As Worksheet: Set ws = ThisWorkbook.Sheets("Sheet1") '<-- Update Sheet
Dim LR As Long
Dim xCell As Range, xRange As Range
Dim DeleteMe As Range


'Assign/Set necessarry variables
LR = ws.Range("A" & ws.Rows.Count).End(xlUp).Row
Set xRange = ws.Range("A2:A" & LR)


'Create Union (Collection Of Rows) to Delete
For Each xCell In xRange
    If Not InStr(xCell.Offset(0, 1), "X") Then
        If Not DeleteMe Is Nothing Then
            Set DeleteMe = Union(xCell, DeleteMe)
        Else
            Set DeleteMe = xCell
        End If
    End If
Next xCell


'Delete the Union
If Not DeleteMe Is Nothing Then
    DeleteMe.EntireRow.Delete
End If

End Sub