How to read filtered rows into array and then write back to sheet using VBA arrays

[chriscorpion786]

Hi All,

I have 100 rows, and 22 columns. Based on a filtered criteria, the rows are filtered. I would like to read the filtered rows using special cells or any other method into an array.
Then write the array (filtered results) back to a sheet.
What is the best method using auto filter or a loop ? to loop through the rows based on the criteria. Which one is fastest ?
Can you please share the code. The difficult part I'm finding is to resize the filtered rows and read them into an array and then back to the sheet.

Thank you,
Mustafa

 

[DanteAmor]

With 100 records I suppose execution is immediate, but you should try 300,000 records and see which one performs better.
I show you 2 examples to filter in column A.

Sub test1()
  With Sheets("Sheet1")
    .Range("A1").AutoFilter 1, "some"
    .AutoFilter.Range.EntireRow.Copy Sheets("Sheet2").Range("A1")
  End With
End Sub

Sub test2()
  Dim a As Variant, b As Variant
  Dim i As Long, j As Long, k As Long
  a = Sheets("Sheet1").Range("A1").CurrentRegion.Value
  ReDim b(1 To UBound(a, 1), 1 To UBound(a, 2))
  For i = 1 To UBound(a, 1)
    If a(i, 1) = "some" Then
      j = j + 1
      For k = 1 To UBound(a, 2)
        b(j, k) = a(i, k)
      Next
    End If
  Next
  Sheets("Sheet2").Range("A1").Resize(j, UBound(b, 2)).Value = b
End Sub

 

[chriscorpion786]

Thank you...Thats perfect!!. Can you please explain what does k do? would k mean the column count ? and b is filling the array correct..
Kindly explain if you would.

Many thanks.

 

[DanteAmor]

Can you please explain what does k do? would k mean the column count ? and b is filling the array correct..
Kindly explain if you would.

It is correct, k goes through the columns from 1 to the last column with data. And it is filling the resulting matrix in 'b'

b(j, k)
j is the row count in matrix b and k is the column count.