|
- Sub test2()
- Dim str$, arr, result()
- Dim i&, j&, k&, lStart&, lEnd&, lTemp&, l&
- str = Sheet6.Range("l1").Value
- If Len(str) = 0 Then
- MsgBox "查找内容为空"
- Exit Sub
- End If
- arr = Sheet6.Range("a1").CurrentRegion.Value
- If Not IsArray(arr) Then
- MsgBox "无数据可操作"
- Exit Sub
- End If
- ReDim result(1 To UBound(arr), 1 To 7)
- For i = LBound(arr) + 1 To UBound(arr)
- If InStr(1, arr(i, 4), str) Then
- lStart = i
- lEnd = i
- '起始行
- If Len(arr(i, 1)) Then
- lStart = i
- Else
- Do
- lStart = lStart - 1
- Loop Until Len(arr(lStart, 1))
- End If
- If i < UBound(arr) Then
- If Len(arr(i + 1, 1)) = 0 Then
- Do While Len(arr(lEnd, 1)) > 0
- lEnd = lEnd + 1
- Loop
- End If
- Debug.Print i, lStart, lEnd
- Else
- If i = UBound(arr) Then
- lStart = i
- If Len(arr(i, 1)) = 0 Then
- Do
- lStart = lStart - 1
- Loop Until Len(arr(lStart, 1))
- Else
- lStart = i
- End If
- End If
- Debug.Print i, lStart, lEnd
- End If
- For lTemp = lStart To lEnd
- k = k + 1
- For l = 1 To 4
- result(k, l) = arr(lTemp, l)
- Next
- For l = 10 To 12
- result(k, l - 5) = arr(lTemp, l)
- Next
- Next
- i = lEnd
- End If
- Next
- Application.ScreenUpdating = False
- Sheet1.Cells(Rows.Count, 1).End(xlUp).Resize(k, UBound(result, 2)).Value = result
- Sheet1.Columns("a:g").AutoFit
- MsgBox "筛选完成"
- End Sub
复制代码 |
|