vba Array help

[Mallesh23]

Hi Team,

I want to filter bank columns for account not opened and copy visible cells into Range ("f2")

I am using autofilter method. to do that task.

Now I want to use Array Method to do that task. what will be the code.

Below is my data

emp ID	Bank Account NO	Bank Name	Salary
Sachin	2283	Hdfc	488660​
Dhoni	4564	ICICI	247388​
Yuvraj	3007	SBI	328538​
Peterson	4547	Account Not Opened	335176​
Gayle	1120	Account Not Opened	338873​
Kohli	4843	ICICI	385516​
Ponting	1299	SBI	187661​
Jaysurya	2622	Account Not Opened	449394​
Hardik	2879	ICICI	205961​

expected output.

emp ID	Bank Name	Salary
Peterson	Account Not Opened	335176​
Gayle	Account Not Opened	338873​
Jaysurya	Account Not Opened	449394​

My Attempated Code Autofilter Method

Sub Macro1()

'Normal Variable

Dim sht As Worksheet
Set sht = ThisWorkbook.Worksheets(1)

Dim lastRow As Long
lastRow = sht.Range("A" & Rows.Count).End(xlUp).Row

Dim Col_bank As Long
Col_bank = Application.WorksheetFunction.Match("Bank Name", sht.Rows(1), 0)

With sht.Range("A1").CurrentRegion

    .AutoFilter field:=Col_bank, Criteria1:="Account Not Opened"

    If .Columns(Col_bank).SpecialCells(12).Count > 1 Then
   
        Union(.Columns(1), .Columns(3), .Columns(4)).Offset(1).SpecialCells(xlCellTypeVisible).Copy
       
        sht.Range("f2").PasteSpecial xlPasteAll
        sht.Cells.AutoFilter
   
    Else
   
        sht.Cells.AutoFilter
   
    End If

End With

End Sub

Thanks
mg

 

[Peter_SSs]

It isn't simple code. Appears your question is very similar to this one. Have a look through that thread and see if you can use/adapt what I did in the last code in post #12 there.

 

[Mallesh23]

Hi Peter,

Finding it difficult to understand.which code will fit into my scenario.

Thanks
Mg

 

[Peter_SSs]

Hi Peter,

Finding it difficult to understand.which code will fit into my scenario.

Thanks
Mg

Modification of Sub RawlinsCross_v3()
Try

Sub Mallesh23()
  Dim aRws As Variant

  With Range("A2", Range("D" & Rows.Count).End(xlUp))
    aRws = Filter(Application.Transpose(Evaluate(Replace("if(#=""Account Not Opened"",Row(#),""x"")", "#", .Columns(3).Address))), "x", False)
    Range("F2:H2").Resize(UBound(aRws) + 1).Value = Application.Index(Cells, Application.Transpose(aRws), Array(1, 3, 4))
  End With
End Sub

 

[Mallesh23]

Hi Peter,

Thank you for your help, it worked giving me correct output.
I tried dictionary method it also worked. Can we use Array here, storing in array and printing in range using Redim like that.

In dictionary Approch, Are there any limitation in storing Items into dictionary.
Range("k2").Resize(dict.Count, 3).Value = Application.Index(dict.Items, 0, 0)

Below is a Table with expected output is in Columns("F,g,h")

Book1
	A	B	C	D	E	F	G	H
1	emp ID	Bank Account NO	Bank Name	Salary		emp ID	Bank Account NO	Salary
2	Sachin	2283	Hdfc	488660		Peterson	Account Not Opened	335176
3	Dhoni	4564	ICICI	247388		Gayle	Account Not Opened	338873
4	Yuvraj	3007	SBI	328538		Jaysurya	Account Not Opened	449394
5	Peterson	4547	Account Not Opened	335176
6	Gayle	1120	Account Not Opened	338873
7	Kohli	4843	ICICI	385516
8	Ponting	1299	SBI	187661
9	Jaysurya	2622	Account Not Opened	449394
10	Hardik	2879	ICICI	205961
Sheet3

Another option with Dictionary, are there any other way

Sub Using_Dictionary()

    Dim ar As Variant
    ar = Range("a2:d10").Value2

    Dim dict As New Scripting.Dictionary


    With dict
        For i = 1 To UBound(ar)
            If ar(i, 3) = "Account Not Opened" Then
                 dict.Add i + 1, Array(ar(i, 1), ar(i, 3), ar(i, 4))
            End If
        Next i
    End With


Range("k2").Resize(dict.Count, 3).Value = Application.Index(dict.Items, 0, 0)


End Sub

Thanks
mg

 

[Peter_SSs]

Can we use Array here, storing in array and printing in range

Sure, but unless you are going to do some other processing of that array data, it is just introducing an extra (unnecessary) step.

Sub Mallesh23_v2()
  Dim aRws As Variant, Ary As Variant

  With Range("A2", Range("D" & Rows.Count).End(xlUp))
    aRws = Filter(Application.Transpose(Evaluate(Replace("if(#=""Account Not Opened"",Row(#),""x"")", "#", .Columns(3).Address))), "x", False)
    Ary = Application.Index(Cells, Application.Transpose(aRws), Array(1, 3, 4))
    Range("F2:H2").Resize(UBound(Ary)).Value = Ary
  End With
End Sub