VBA Array - store visible rows into Array

[Mallesh23]

Hi Team,

I want to autofilter on Column("a"), Criteria1:= Array("Sachin","Dhoni") and store visible rows into array.
or Without autofilter Add complete row of data to array where Criteria is met.

and Print from Array to worksheet. Want to learn array here. Thanks



Below is my attempted Code... gives correct output. But want output into Array here.

 Sub Array_Help()

    Dim arr As Variant
    Dim rg As Range
    Dim coll As New Collection
    Dim sht As Worksheet
    Set sht = ThisWorkbook.Worksheets(1)
   
    arr = Join(Array("Sachin", "Dhoni"), "!")
    Set rg = sht.Range("A1").CurrentRegion
   
    Dim i As Long
    'Add Data to Dictionary
   
    For i = 2 To rg.Rows.Count
        If InStr(1, arr, rg.Cells(i, 1), vbTextCompare) > 0 Then
            coll.Add rg.Rows(i).Value
        End If
    Next i


    Dim item As Variant
    Dim row As Long
    row = 2
    Dim columns As Long
   
    'Print data
     For Each item In coll
        columns = UBound(item, 2)
        sht.Cells(row, 11).Resize(1, columns).Value = item
        row = row + 1
    Next

End Sub

Dummy Table.

Array_help.xlsm
	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R
1	aaa	bbb	ccc	ddd	eee	fff	ggg		Expected Output	Name	aaa	bbb	ccc	ddd	eee	fff	ggg
2	aaa	bbb	ccc	ddd	eee	fff	ggg			Sachin	aaa	bbb	ccc	ddd	eee	fff	ggg
3	aaa	bbb	ccc	ddd	eee	fff	ggg			Dhoni	aaa	bbb	ccc	ddd	eee	fff	ggg
4	aaa	bbb	ccc	ddd	eee	fff	ggg			Sachin	aaa	bbb	ccc	ddd	eee	fff	ggg
5	aaa	bbb	ccc	ddd	eee	fff	ggg			Dhoni	aaa	bbb	ccc	ddd	eee	fff	ggg
6	aaa	bbb	ccc	ddd	eee	fff	ggg			Sachin	aaa	bbb	ccc	ddd	eee	fff	ggg
7	aaa	bbb	ccc	ddd	eee	fff	ggg			Dhoni	aaa	bbb	ccc	ddd	eee	fff	ggg
8	aaa	bbb	ccc	ddd	eee	fff	ggg
9	aaa	bbb	ccc	ddd	eee	fff	ggg
10	aaa	bbb	ccc	ddd	eee	fff	ggg
11	aaa	bbb	ccc	ddd	eee	fff	ggg
12	aaa	bbb	ccc	ddd	eee	fff	ggg
Sheet1

Thanks
mg

 

[Fluff]

How do you know it gives the correct output? Every row is exactly the same!!
Also you have not included col A.

 

[Mallesh23]

Hi Fluff,

Column A is a list of Player Name, I want data of Sachin and Dhoni's data as an ouput.

Manually It will be autofilter Sachin and Dhoni in Column A, and copy visible Data into column K2 onwards.

Not complete row here, as output is in same sheet.



Thanks
mg

 

[Fluff]

But there is no column A in your data!!!!

 

[Mallesh23]

Hi fluff,

OMG ! my mistake, Column A was missing, Thanks for highlighting.

here is below updated data. Thanks.

Array_help.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R
1	Name	aaa	bbb	ccc	ddd	eee	fff	ggg		Expected Output	Name	aaa	bbb	ccc	ddd	eee	fff	ggg
2	Sachin	aaa	bbb	ccc	ddd	eee	fff	ggg			Sachin	aaa	bbb	ccc	ddd	eee	fff	ggg
3	Dhoni	aaa	bbb	ccc	ddd	eee	fff	ggg			Dhoni	aaa	bbb	ccc	ddd	eee	fff	ggg
4	Sachin	aaa	bbb	ccc	ddd	eee	fff	ggg			Sachin	aaa	bbb	ccc	ddd	eee	fff	ggg
5	Dhoni	aaa	bbb	ccc	ddd	eee	fff	ggg			Dhoni	aaa	bbb	ccc	ddd	eee	fff	ggg
6	Virat	aaa	bbb	ccc	ddd	eee	fff	ggg			Sachin	aaa	bbb	ccc	ddd	eee	fff	ggg
7	Sachin	aaa	bbb	ccc	ddd	eee	fff	ggg			Dhoni	aaa	bbb	ccc	ddd	eee	fff	ggg
8	Hardik	aaa	bbb	ccc	ddd	eee	fff	ggg
9	Dhoni	aaa	bbb	ccc	ddd	eee	fff	ggg
10	Kapil	aaa	bbb	ccc	ddd	eee	fff	ggg
11	Kapil	aaa	bbb	ccc	ddd	eee	fff	ggg
12	Yuvraj	aaa	bbb	ccc	ddd	eee	fff	ggg
Sheet1

Thanks
mg

 

[Fluff]

This is totally untested, as there is no point in testing against that data.

Sub Mallesh()
   Dim Ary As Variant, Nary As Variant
   Dim r As Long, c As Long, nr As Long
   
   Ary = Sheets("Sheet1").Range("A1").CurrentRegion.Value2
   ReDim Nary(1 To UBound(Ary), 1 To UBound(Ary, 2))
   For r = 1 To UBound(Ary)
      If Ary(r, 1) = "Sachin" Or Ary(r, 1) = "Dhoni" Then
         nr = nr + 1
         For c = 1 To UBound(Ary, 2)
            Nary(nr, c) = Ary(r, c)
         Next c
      End If
   Next r
   Sheets("Sheet1").Range("K2").Resize(nr, UBound(Ary, 2)).Value = Nary
End Sub

 

[Mallesh23]

Hi Fluff,

Awsomeeee ......Tested and It worked as expected, Thanks for your help.?



Thanks
mg

 

[Fluff]

You're welcome & thanks for the feedback.