感觉使用重新定义的数组比区域写入的数组速度要快些,请和上面数组法比较一下速度:- Private Sub CommandButton2_Click() '数组法
- Dim arr, brr(), f(1 To 5) As Boolean, t, i&, j&, m&, lr&, lc&
- t = Range("A2:F2")
- For i = 1 To 5 Step 2
- If Len(t(1, i)) Then f(i) = True
- Next
- If Not (f(1) Or f(3) Or f(5)) Then Exit Sub
- With Sheets("材料价格表")
- arr = .Range("A1").CurrentRegion
- lr = .Range("b" & Rows.Count).End(xlUp).Row
- lc = UBound(arr, 2)
- ReDim brr(1 To lr, 1 To lc) '重新定义的数组
- For i = 2 To lr
- If ((f(1) And InStr(arr(i, 2), t(1, 1)) > 0) Or Not f(1)) _
- And ((f(3) And InStr(arr(i, 3), t(1, 3)) > 0) Or Not f(3)) _
- And ((f(5) And InStr(arr(i, 5), t(1, 5)) > 0) Or Not f(5)) Then
- m = m + 1
- For j = 1 To lc
- brr(m, j) = arr(i, j)
- Next
- End If
- Next
- End With
- Range("A5:G" & Rows.Count).ClearContents
- If m Then [a5].Resize(m, j - 1) = brr
- End Sub
复制代码 |