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
expected output.
My Attempated Code Autofilter Method
Thanks
mg
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
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