基于部分文本删除行 [英] Delete row based on partial text

问题描述

如果在 A 列中找到文本FemImplant"，我将尝试删除整行.

I am trying to delete the entire row if I find the text 'FemImplant' in column A.

文本是由$"链接的句子的一部分.我需要解析 '$' 之前的单元格内容，看看它是否匹配 'FemImplant' 并删除该行.

The text is part of a sentence linked by '$'. I need to parse the cell content before '$' and see if it matches 'FemImplant' and delete that row.

这是我目前所拥有的.

Dim cell As Excel.Range
RowCount = DataSheet.UsedRange.Rows.Count
Set col = DataSheet.Range("A1:A" & RowCount)
Dim SheetName As String
Dim ParsedCell() As String

For Each cell In col
    ParsedCell = cell.Value.Split("$")
    SheetName = ParsedCell(0)

    If SheetName = "FemImplant" Then
        cell.EntireRow.Delete Shift:=xlUp
    End If
Next

推荐答案

您可以使用 AutoFilter 删除包含文本 FemImplant$ 的行.这种方法会比循环快得多.

You can use AutoFilter to delete the rows which contain the text FemImplant$. This method will be much faster than looping.

如果您正在使用 Boolean 值，那么您可能希望查看 尝试删除我的范围内具有错误值的行

If you are working with Boolean values then you may want to see Trying to Delete Rows with False Value in my Range

看这个例子

我假设 Cell A1 有标题.

I am assuming that Cell A1 has header.

Sub Sample()
    Dim ws As Worksheet
    Dim strSearch As String
    Dim lRow As Long
    
    strSearch = "FemImplant$"
    
    Set ws = Sheets("Sheet1")
    
    With ws
        lRow = .Range("A" & .Rows.Count).End(xlUp).Row
        
        '~~> Remove any filters
        .AutoFilterMode = False
        
        '~~> Filter, offset(to exclude headers) and delete visible rows
        With .Range("A1:A" & lRow)
          .AutoFilter Field:=1, Criteria1:="=*" & strSearch & "*"
          .Offset(1, 0).SpecialCells(xlCellTypeVisible).EntireRow.Delete
        End With

        '~~> Remove any filters
        .AutoFilterMode = False
    End With
End Sub

快照

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