删除它下面包含关键字和缩进记录的行。
删除它下面包含关键字和缩进记录的行。
提问于 2021-08-23 19:13:33
附件是我的数据的一个小样本，第7、8、12和13行是父标题，第9-11行是第8行的子标题，因为它是缩进的。当我运行Range().IndentLevel时，它对第7行、第8行、第12行、第13行和第3行返回2( 9-11行)。这是列中仅有的两个IndentLevels
我试图删除所有带有关键字“追踪调整”的行，这是我能够用以下方法完成的：
Dim ws As Worksheet
Dim strSearch As String
Dim lRow As Long
strSearch = "Pursuit Adjustment"
Set ws = Sheets("PFSR All (formatted)")
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然而，我很难搞清楚两件事：
- 如何删除
- (第9-11行)中关联父标题的所有子标题？
-
strSearch中的 - ，我只指定了“跟踪调整”，如何向其添加多个搜索条件？
回答 2
Stack Overflow用户
回答已采纳
发布于 2021-08-24 01:42:59
您可以尝试以下代码：
Dim ws As Worksheet
Dim i As Long, j As Long, lRow As Long
Dim strSearch As Variant
strSearch = Array("Pursuit Adjustment", "str2", "str3") 'Put here all the strings you want to search and delete
Set ws = Sheets("PFSR All (formatted)")
With ws
lRow = .Range("A" & .Rows.Count).End(xlUp).Row
.AutoFilterMode = False
For i = LBound(strSearch) To UBound(strSearch)
For j = 1 To lRow
If InStr(.Range("A" & j), strSearch(i)) Then
.Rows(j).EntireRow.Delete
Do While .Range("A" & j).IndentLevel > 2
.Rows(j).EntireRow.Delete
Loop
j = j - 1
End If
Next j
Next i
End WithStack Overflow用户
发布于 2021-08-24 07:00:29
请也试试下一段代码。它会更快，将必要的范围放置在一个Union范围内，并在结束时立即删除它们。实际代码只选择要删除的行。如果它返回所需的内容，则可以在最后一行代码中用Select替换Delete：
Sub teleteSpecificRowsAndIndentedBelow()
Dim ws As Worksheet, strSearch As String, lRow As Long, arrA, arrSearch, El
Dim i As Long, j As Long, rngDel As Range, boolFound As Boolean
arrSearch = Split("Pursuit Adjustment,second string,third string,etc", ",") 'no space after comma!!!
Set ws = ActiveSheet ' Sheets("PFSR All (formatted)")
lRow = ws.Range("A" & ws.rows.count).End(xlUp).row
arrA = ws.Range("A1:A" & lRow).Value 'put it in an array to make iteration faster
For i = 1 To UBound(arrA)
For Each El In arrSearch
If InStr(arrA(i, 1), El) > 0 Then boolFound = True: Exit For
Next
If boolFound Then
If rngDel Is Nothing Then
Set rngDel = ws.Range("A" & i)
Else
Set rngDel = Union(rngDel, ws.Range("A" & i))
End If
'start searching for indented following rows:
For j = 1 To lRow
If ws.Range("A" & i + j).IndentLevel < 2 Then Exit For
Set rngDel = Union(rngDel, ws.Range("A" & i + j))
Next j
i = i + j - 1: boolFound = False
End If
Next i
If Not rngDel Is Nothing Then rngDel.EntireRow.Select 'if the output is correct, please replace Select with Delete
End Sub页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/68897909
复制相关文章
相似问题
腾讯云开发者
