vba Array help

Mallesh23

Well-known Member
Joined
Feb 4, 2009
Messages
843
Office Version
  1. 2010
Platform
  1. Windows
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 IDBank Account NOBank NameSalary
Sachin2283Hdfc
488660​
Dhoni4564ICICI
247388​
Yuvraj3007SBI
328538​
Peterson4547Account Not Opened
335176​
Gayle1120Account Not Opened
338873​
Kohli4843ICICI
385516​
Ponting1299SBI
187661​
Jaysurya2622Account Not Opened
449394​
Hardik2879ICICI
205961​


expected output.

emp IDBank NameSalary
PetersonAccount Not Opened
335176​
GayleAccount Not Opened
338873​
JaysuryaAccount Not Opened
449394​


My Attempated Code Autofilter Method

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

Attachments

  • Bank Account.PNG
    Bank Account.PNG
    22.4 KB · Views: 3

Some videos you may like

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
47,926
Office Version
  1. 365
Platform
  1. Windows
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

Well-known Member
Joined
Feb 4, 2009
Messages
843
Office Version
  1. 2010
Platform
  1. Windows
Hi Peter,

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

Thanks
Mg
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
47,926
Office Version
  1. 365
Platform
  1. Windows
Hi Peter,

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

Thanks
Mg
Modification of Sub RawlinsCross_v3()
Try

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

Well-known Member
Joined
Feb 4, 2009
Messages
843
Office Version
  1. 2010
Platform
  1. Windows
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
ABCDEFGH
1emp IDBank Account NOBank NameSalaryemp IDBank Account NOSalary
2Sachin2283Hdfc488660PetersonAccount Not Opened335176
3Dhoni4564ICICI247388GayleAccount Not Opened338873
4Yuvraj3007SBI328538JaysuryaAccount Not Opened449394
5Peterson4547Account Not Opened335176
6Gayle1120Account Not Opened338873
7Kohli4843ICICI385516
8Ponting1299SBI187661
9Jaysurya2622Account Not Opened449394
10Hardik2879ICICI205961
Sheet3




Another option with Dictionary, are there any other way
VBA Code:
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

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
47,926
Office Version
  1. 365
Platform
  1. Windows
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.

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

Watch MrExcel Video

Forum statistics

Threads
1,123,426
Messages
5,601,595
Members
414,462
Latest member
StageRiis

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