vba 基于部分文本删除行
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/11317172/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Delete row based on partial text
提问by user1452091
I am trying to delete the entire row if I find the text 'FemImplant' in column A.
如果在 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.
文本是由“$”链接的句子的一部分。我需要在 '$' 之前解析单元格内容，看看它是否匹配 'FemImplant' 并删除该行。
This is what I have so far.
这是我到目前为止。
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
回答by Siddharth Rout
You can use AutoFilter to delete the rows which contain the text FemImplant$. This method will be much faster than looping.
您可以使用 AutoFilter 删除包含文本的行FemImplant$。这种方法将比循环快得多。
See this example
看这个例子
I am assuming that Cell A1 has header.
我假设 Cell A1 有标题。
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
SNAPSHOT
快照
