Option Explicit
Sub 筛选()
Dim i As Integer, j As Integer, index As Integer, w1 As Worksheet, w2 As Worksheet
Set w1 = Worksheets("Sheet1")
Set w2 = Worksheets("Sheet2")
i = 2
Do While Trim(w1.Cells(i, 3)) <> ""
For j = 2 To 74 Step 1
index = InStr(w1.Cells(i, 3), w1.Cells(j, 7))
If index > 0 Then
w2.Cells(i, 1) = w1.Cells(i, 1)
w2.Cells(i, 2) = w1.Cells(i, 2)
w2.Cells(i, 3) = w1.Cells(i, 3)
w2.Cells(i, 4) = w1.Cells(i, 4)
w2.Cells(i, 5) = w1.Cells(i, 5)
End If
Next j
i = i + 1
Loop
Sheets("Sheet2").Select
Range(Cells(1, 1), Cells(i, 5)).Select
Selection.SpecialCells(xlCellTypeBlanks).Select
Selection.Delete Shift:=xlUp
End Sub |