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

chriscorpion786

Board Regular
Joined
Apr 3, 2011
Messages
100
Office Version
  1. 365
Platform
  1. Windows
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
 

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
14,155
Office Version
  1. 2007
Platform
  1. Windows
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.

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


VBA Code:
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

Board Regular
Joined
Apr 3, 2011
Messages
100
Office Version
  1. 365
Platform
  1. Windows
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

Well-known Member
Joined
Dec 3, 2018
Messages
14,155
Office Version
  1. 2007
Platform
  1. Windows
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.
 
Solution
Learn Excel from Bill Jelen

Understanding data is crucial, and the easiest place to start is with Microsoft Excel.

Forum statistics

Threads
1,151,589
Messages
5,765,320
Members
425,273
Latest member
tonio909

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top