Hi,
Sheet1 got a list of data (estimate around 50k)
Expected Result: Filter No: 123 data into array (cArr)
I've been cleaning those datas by using Split and Join in order to get 123 data in cArr, However, its way too slow to execute and many inefficient code, Is there any more minimalism code for this kind of case?
Btw, Is that possible to filter with two criterias then store in the array as well ? Thankyou
Thank you ~
Sheet1 got a list of data (estimate around 50k)
Expected Result: Filter No: 123 data into array (cArr)
I've been cleaning those datas by using Split and Join in order to get 123 data in cArr, However, its way too slow to execute and many inefficient code, Is there any more minimalism code for this kind of case?
Btw, Is that possible to filter with two criterias then store in the array as well ? Thankyou
Thank you ~
Book2 | ||||||||
---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | |||
1 | NO | TES | RATIO | CONTROL | PCT | AMOUNT | ||
2 | 123 | OK | 3 | 4 | 5 | 2323 | ||
3 | 33 | OK | 3 | 4 | 5 | 1 | ||
4 | 2 | OK | 3 | 4 | 5 | 2 | ||
5 | 2 | OK | 3 | 4 | 5 | 5 | ||
6 | 1 | OK | 3 | 4 | 5 | 4 | ||
7 | 2 | not ok | 3 | 4 | 5 | 6 | ||
8 | 2 | not ok | 3 | 4 | 5 | 7 | ||
9 | 3 | not ok | 3 | 4 | 5 | 8 | ||
10 | 44 | not ok | 3 | 4 | 5 | 9 | ||
11 | 123 | not ok | 3 | 4 | 5 | 123 | ||
12 | 44 | pending | 3 | 4 | 5 | 101 | ||
13 | 44 | pending | 3 | 4 | 5 | 11 | ||
14 | 123 | pending | 3 | 4 | 5 | 58 | ||
Sheet1 |
VBA Code:
Sub testarr()
Dim i%
Dim aArr(), cArr()
Set found = Range("A1:aa100").Find("pct") 'This one because only want to get the datas until PCT Column
For Each ss In Range("a2", [a2].End(xlDown))
n = n + 1
ReDim Preserve aArr(1 To n)
aArr(n) = Join(Application.Transpose(Application.Transpose(ss.Resize(1, found.Column))), "#")
Next ss
bArr = Filter(aArr, "123")
ReDim cArr(1 To UBound(bArr) + 1)
For Each aR In bArr
k = k + 1
cArr(k) = Split(aR, "#")
Next aR
End Sub