EXCEL两个条件筛选100万行的VBA代码
发布网友
发布时间:2022-04-30 03:26
我来回答
共2个回答
热心网友
时间:2023-04-25 21:08
试下
Sub 条件筛选()
Dim i&, j&, k%
Dim arr As Variant
Dim arr1(1 To 1000000, 1 To 2)
arr = Range("A2:D" & [A1048576].End(xlUp).Row)
For i = 1 To UBound(arr)
If arr(i, 1) & arr(i, 2) = Cells(1, "E") & Cells(1, "f") Then
k = k + 1
arr1(k, 1) = arr(i, 3)
arr1(k, 2) = arr(i, 4)
End If
Next i
Cells(2, "h").Resize(UBound(arr1), 2) = arr1
End Sub
可以将A+B 作为排序条件,A:D排序
然后获取E1+F1开始的行号,和结束行,直接获取这个区域的数据,复制到H2来自:求助得到的回答
热心网友
时间:2023-04-25 21:08
写代码来实现追问请问这个代码怎么修改?
Sub 条件筛选()Dim i&, j&, k%Dim arr As VariantDim arr1(1 To 1000000, 1 To 2) arr = [a2:d1000000] For i = 1 To UBound(arr)If arr(i, 1) "" ThenIf arr(i, 1) = Cells(1, "e") And arr(i, 2) = Cells(1, "f") Thenk = k + 1arr1(k, 1) = arr(i, 3)arr1(k, 2) = arr(i, 4)End IfEnd IfNext iCells(2, "h").Resize(UBound(arr1), 2) = arr1 End Sub
热心网友
时间:2023-04-25 21:08
试下
Sub 条件筛选()
Dim i&, j&, k%
Dim arr As Variant
Dim arr1(1 To 1000000, 1 To 2)
arr = Range("A2:D" & [A1048576].End(xlUp).Row)
For i = 1 To UBound(arr)
If arr(i, 1) & arr(i, 2) = Cells(1, "E") & Cells(1, "f") Then
k = k + 1
arr1(k, 1) = arr(i, 3)
arr1(k, 2) = arr(i, 4)
End If
Next i
Cells(2, "h").Resize(UBound(arr1), 2) = arr1
End Sub
可以将A+B 作为排序条件,A:D排序
然后获取E1+F1开始的行号,和结束行,直接获取这个区域的数据,复制到H2来自:求助得到的回答
热心网友
时间:2023-04-25 21:08
写代码来实现追问请问这个代码怎么修改?
Sub 条件筛选()Dim i&, j&, k%Dim arr As VariantDim arr1(1 To 1000000, 1 To 2) arr = [a2:d1000000] For i = 1 To UBound(arr)If arr(i, 1) "" ThenIf arr(i, 1) = Cells(1, "e") And arr(i, 2) = Cells(1, "f") Thenk = k + 1arr1(k, 1) = arr(i, 3)arr1(k, 2) = arr(i, 4)End IfEnd IfNext iCells(2, "h").Resize(UBound(arr1), 2) = arr1 End Sub