希望有人可以幫助我修復此代碼。想要在 F 列中查找字串“StringName”（該字串將始終在 F 列中）。我試圖制作兩個作業表的陣列，然后遍歷它們并找到我要洗掉的字串。如果在一張或兩張作業表中找到該字串，則應洗掉整行。
我想用另外 4 個字串來做這個，還沒有想過怎么做。找到我需要保留的字串“hello”和“goodbye”，然后說出與這兩個字串不匹配的所有內容，洗掉會更好嗎？希望有人能幫忙
Sub test1()
Dim sheetArray As Variant
Dim ws As Variant
Dim targetCell As Range
sheetArray = Array("Sheet1", "Sheet2")
For Each ws In sheetArray
With Worksheets(ws)
For Each targetCell In Range("F:F")
If InStr(targetCell, "StringName") Then
targetCell.EntireRow.delete
End If
Next targetCell
End With
Next ws
End Sub
uj5u.com熱心網友回復：
我使用了自動過濾器并洗掉了可見行的行。如果沒有標題，則不需要偏移量。我在代碼中使用了字串不匹配 sheet1 中的條件和 Sheet2 中的字串匹配條件
Sub autofilter_Remove()
Dim wb As Workbook
Dim ws1, ws2 As Worksheet
Dim Rng1, Rng2 As Range
Set wb = ThisWorkbook
Set ws1 = wb.Worksheets("Sheet1")
Set ws2 = wb.Worksheets("Sheet2")
Set Rng1 = ws1.Rows(1)
Set Rng2 = ws2.Rows(1)
Stringname = "hello"
'6 for F column
Rng1.AutoFilter Field:=6, Criteria1:="<>*" & Stringname & "*"
ws1.UsedRange.Offset(1, 0).SpecialCells _
(xlCellTypeVisible).EntireRow.Delete
ws1.Cells.AutoFilter
Rng2.AutoFilter Field:=6, Criteria1:="*" & Stringname & "*"
ws2.UsedRange.Offset(1, 0).SpecialCells _
(xlCellTypeVisible).EntireRow.Delete
ws2.Cells.AutoFilter
End Sub
uj5u.com熱心網友回復：
使用If ... And ... 或If ... Or ...。下面的示例，我只按照您的建議保留“你好”和“再見”，因為這樣代碼更短/更簡單。
Sub answer1()
Dim sheetArray As Variant, ws As String, a As Long
sheetArray = Array("Sheet1", "Sheet2")
For Each ws In sheetArray
With Worksheets(ws)
For a = .UsedRange.Rows.Count UsedRange.Row -1 to .UsedRange.Row step -1 'counting backwards/upwards because we're deleting rows, counting forward/down would end up skipping some rows.
If Not .Cells(a, 6) Like "*hello*" _
And Not .Cells(a, 6) Like "*goodbye*" Then 'the 6 refers to column F
.Rows(a).Delete
End If
Next
End With 'Worksheets(ws)
Next ws
End Sub
轉載請註明出處，本文鏈接：https://www.uj5u.com/houduan/322013.html
下一篇：在回圈中檢索索引時出現問題