CODEWITHSUNDEEP
arraysexcelautofiltervba
Amatya
Amatya

Reputation: 1243

Excel VBA AutoFilter Array error

My data is like follows:

Name Value Flag

Alice 5 BUYBACK

Bob 8 Jump

Dan 9

Alice 1

Alice 6 Jump

Dan 0 BUYBACK

Bob 8 Jump

If for any Name, in any record, in the Flag field if there is a "BUYBACK" then I want to preserve all the records associated with that name. Alice has 3 records and one of them is BUYBACK so I want to keep all of ALice's records. Dan has a buy back in one of the two so I want to keep both of Dan's records while both of Bob's records get deleted.

When I try to do it below, I get an error in my AutoFilter. The error says "Object required". I can't seem to figure out what's wrong.

 n = 1

     Dim BBK_Array() As Variant

     For j = 1 To FinalRow

        If Cells(j, 3).Value = "BUYBACK" Then
            If n = 1 Then
                 ReDim Preserve BBK_Array(1 To n)
                 BBK_Array(n) = Cells(j, 1).Value
                 n = n + 1

            ElseIf BBK_Array(n - 1) <> Cells(j, 1).Value Then
                    ReDim Preserve BBK_Array(1 To n)
                    BBK_Array(n) = Cells(j, 1).Value
                    n = n + 1

            End If


        End If
     Next j




     ActiveWorksheet.UsedRange.AutoFilter Field:=1, Criteria1:=BBK_Array(), Operator:=xlFilterValues

EDIT:

When I did this, it worked. I don't know why:

     ActiveWorkbook.Activesheet.UsedRange.AutoFilter Field:=1, Criteria1:=BBK_Array(), Operator:=xlFilterValues

Upvotes: 0

Views: 631

Answers (1)

Siddharth Rout
Siddharth Rout

Reputation: 149295

Do you need VBA for this? A simple Excel formula can solve your problem.

Let's say your data looks like this

enter image description here

All you need to do is create a 4th column and enter is formula in cell D2

=SUMPRODUCT((A:A=A2)*(C:C="BuyBack"))

Simply auto fill the formula to the last row. Now filter col D on 0 and delete it.

enter image description here

See this.

enter image description here

Once done, delete col D

If you still want a VBA solution then record a macro and follow the above steps and simply amend it to suit your needs. It will be 9 lines of code (including the declarations)

Here is an example

Sub Sample()
    Dim ws As Worksheet: Set ws = ThisWorkbook.Sheets("Sheet1")
    Dim lRow As Long: lRow = ws.Range("A" & ws.Rows.Count).End(xlUp).Row

    ws.Range("D2:D" & lRow).Formula = "=SUMPRODUCT((A:A=A2)*(C:C=""BuyBack""))"
    ws.Range("D2:D" & lRow).Value = ws.Range("D2:D" & lRow).Value

    ws.AutoFilterMode = False
    ws.Range("D2:D" & lRow).AutoFilter Field:=1, Criteria1:="=0"
    ws.Range("D2:D" & lRow).Offset(1, 0).SpecialCells(xlCellTypeVisible).EntireRow.Delete
    ws.AutoFilterMode = False

    ws.Columns(4).Delete
End Sub

And this is the output we get after the macro is run.

enter image description here

Upvotes: 1

Related Questions