根据多个条件中的条件删除行
我使用此代码查找在单元格(10,2)中写入同一工作簿中多个表格的不同行中的单词,并且当找到单词时,代码将删除每个表中的整行,问题是该代码应用在第一个工作表中的命令按钮处于打开状态,并且未应用于其他工作表,因此请在此处给予帮助。根据多个条件中的条件删除行
sub Deletrows_Click()
Dim WS As Worksheet
Dim pattern As String
For Each WS In ThisWorkbook.Worksheets
With WS
pattern = Cells(10, 2) ' delete row if found the word total in it
RowCount = ActiveSheet.UsedRange.Rows.Count
Dim i As Integer
For i = 2 To RowCount
Dim j As Integer
For j = 1 To 3 'find the word within this range
If Cells(i, j) = pattern Then
Cells(i, j).EntireRow.Delete
End If
Next j
Next i
End With
Next WS
End Sub
您需要通过添加.
作为前缀完全限定所有Range
和Cells
的With WS
语句中。
E.g.而不是pattern = Cells(10, 2)
使用pattern = .Cells(10, 2)
,.Cells(10, 2)
意味着WS
的单元格(10,2),它在For Each WS In ThisWorkbook.Worksheets
中正在进行中。
代码
Option Explicit
Sub Deletrows_Click()
Dim WS As Worksheet
Dim pattern As String
Dim RowCount As Long, i As Long, j As Long
For Each WS In ThisWorkbook.Worksheets
With WS
pattern = .Cells(10, 2) ' delete row if found the word total in it
RowCount = .UsedRange.Rows.Count
For i = 2 To RowCount
For j = 1 To 3 'find the word within this range
If .Cells(i, j) = pattern Then
.Cells(i, j).EntireRow.Delete
End If
Next j
Next i
End With
Next WS
End Sub
选项2:不要使用两个For
循环,你可以用Application.Match
功能取代第二For
循环,以寻找一个特定值在整个行。与之相匹配的
Option Explicit
Sub Deletrows_Click()
Dim WS As Worksheet
Dim pattern As String
Dim RowCount As Long, i As Long, j As Long
For Each WS In ThisWorkbook.Worksheets
With WS
pattern = .Cells(10, 2) ' delete row if found the word total in it
RowCount = .UsedRange.Rows.Count
For i = 2 To RowCount
' use the Match function to find the word inside a certain row
If Not IsError(Application.Match(pattern, .Range(.Cells(i, 1), .Cells(i, 3)), 0)) Then '<-- match was successful
.Cells(i, 1).EntireRow.Delete
End If
Next i
End With
Next WS
End Sub
编辑
代码2:
Option Explicit
Sub Deletrows_Click()
Dim WS As Worksheet
Dim pattern As String
Dim FirstRow As Long, RowCount As Long, i As Long, j As Long
Dim FirstCol, ColCount As Long
For Each WS In ThisWorkbook.Worksheets
With WS
pattern = .Cells(10, 2) ' delete row if found the word total in it
FirstRow = .UsedRange.Row
RowCount = .UsedRange.Rows.Count
FirstCol = .UsedRange.Column
ColCount = .UsedRange.Columns.Count
For i = 2 To RowCount + FirstRow
' use the Match function to find the word inside a certain row
If Not IsError(Application.Match(pattern, .Range(.Cells(i, 1), .Cells(i, ColCount + FirstCol)), 0)) Then '<-- match was successful
.Cells(i, 1).EntireRow.Delete
End If
Next i
End With
Next WS
End Sub
首先,感谢你的善意帮助。我尝试了第二个代码,它的效果很好,但它仅删除了所有工作表中的单元格(10,2)中的匹配单词,并忽略了其他列或行中的任何其他类似单词。更好地表达自己。说我想删除任何有红苹果这个词的表中的任何一行。那么请你协助? –
@NabilAmer你想从整个工作表中删除'Cells(10,2)'中的单词吗?导致你的帖子你只看A到C列 –
嗨嗨,我试过编辑2,它只在单元格(10,2)有要求的单词时才有效,但如果没有找到,它就不能在其他单上使用。单元格(10,2)在sheet1中需要排除,所以我添加了下面的代码,它可以根据需要工作(pattern = Sheets(“Sheet1”)。单元格(10,2)以及这个(如果WS。如果可以的话,我还有一个问题,如何在不要求我重复输入所需数据的情况下如何使用输入框,因为我使用的代码也是pattern = InputBox (“请输入所选的ID”) –
Sub Deletrows_Click()
Dim WS As Worksheet
Dim pattern As String
Dim FirstRow As Long, RowCount As Long, i As Long, j As Long
Dim FirstCol, ColCount As Long
For Each WS In ThisWorkbook.Worksheets
With WS
pattern = Sheets("Sheet1").Cells(10, 2) ' delete row if found the word in this source sheet
FirstRow = .UsedRange.Row
RowCount = .UsedRange.Rows.Count
FirstCol = .UsedRange.Column
ColCount = .UsedRange.Columns.Count
For i = 2 To RowCount + FirstRow
' use the Match function to find the word inside a certain row
If WS.Name <> "Sheet1" Then 'I added this to exclude the said sheet as a source page
If Not IsError(Application.Match(pattern, .Range(.Cells(i, 1), .Cells(i, ColCount + FirstCol)), 0)) Then '<-- match was successful
.Cells(i, 1).EntireRow.Delete
End If
End If
Next i
End With
Next WS
End Sub
非常感谢夏嘉曦的善意帮助,它帮助了很多 –