Filtering Array Data

[RudRud]

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 ~

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

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

 

[DanteAmor]

Try the following code.
In this way you only need 2 arrays, an array with the original data, this is done to read the data from memory and not from the sheet, this makes it faster; and the second array is to put the data that meets the condition.

If you use Application.Transpose you are using a sheet function, this slows down the process, so it is preferable to read all the columns, it does not matter if there are many columns, since the reading is done in memory it is not slow.

Sub testarr_2()
  Dim a As Variant, b As Variant
  Dim i As Long, j As Long, k As Long
  Dim f As Range
  
  Set f = Range("A1:aa100").Find("pct", , xlValues, xlPart, , , False)
  If Not f Is Nothing Then
    a = Range("A2", Cells(Range("A" & Rows.Count).End(3).Row, f.Column)).Value
    ReDim b(1 To UBound(a, 1), 1 To f.Column)
    
    For i = 1 To UBound(a, 1)
      If a(i, 1) = 123 Then
        k = k + 1
        For j = 1 To UBound(a, 2)
          b(k, j) = a(i, j)
        Next
      End If
    Next
  Else
    MsgBox "The text 'pct' does not exist"
  End If
End Sub

Notes:
Check if the search for "pct" is partial or the value is in the whole cell. If it is in the whole cell in the Find method change xlPart to xlWhole.

--------------
Let me know the result and I'll get back to you as soon as I can.
Sincerely
Dante Amor
--------------

 

[RudRud]

Try the following code.
In this way you only need 2 arrays, an array with the original data, this is done to read the data from memory and not from the sheet, this makes it faster; and the second array is to put the data that meets the condition.

If you use Application.Transpose you are using a sheet function, this slows down the process, so it is preferable to read all the columns, it does not matter if there are many columns, since the reading is done in memory it is not slow.

Sub testarr_2()
  Dim a As Variant, b As Variant
  Dim i As Long, j As Long, k As Long
  Dim f As Range
 
  Set f = Range("A1:aa100").Find("pct", , xlValues, xlPart, , , False)
  If Not f Is Nothing Then
    a = Range("A2", Cells(Range("A" & Rows.Count).End(3).Row, f.Column)).Value
    ReDim b(1 To UBound(a, 1), 1 To f.Column)
   
    For i = 1 To UBound(a, 1)
      If a(i, 1) = 123 Then
        k = k + 1
        For j = 1 To UBound(a, 2)
          b(k, j) = a(i, j)
        Next
      End If
    Next
  Else
    MsgBox "The text 'pct' does not exist"
  End If
End Sub

Notes:
Check if the search for "pct" is partial or the value is in the whole cell. If it is in the whole cell in the Find method change xlPart to xlWhole.

--------------
Let me know the result and I'll get back to you as soon as I can.
Sincerely
Dante Amor
--------------

Hi Sir, Excuse my late response.

Thank you for sharing your elegant code and being so kind to include an explanation. It means a lot more than I had previously imagined. Truly Appreacitaed

will start using this way to clean up my arrays