Excel VBA AutoFilter Array error

Question

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

@Gary's Student .. earlier in the code. When I did Activeworkbook.activesheet then it worked. — Amatya
– Amatya, Commented Nov 20, 2013 at 21:16
OK I guess my sheet wasn't active or something. Should I delete the question? Can you tell me why activeworkbook.activesheet worked but just activesheek didn't? I wrote the macro in the same file in which the data is kept. thanks — Amatya
– Amatya, Commented Nov 20, 2013 at 21:18
Don't delete the question.....fix the problems as you find them.......also fix the spelling of BuyBack v.s. BUYBACK — Gary's Student
– Gary's Student, Commented Nov 20, 2013 at 21:23

Siddharth Rout · Accepted Answer · 2013-11-20 21:50:28Z

1

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

edited Nov 20, 2013 at 21:50

answered Nov 20, 2013 at 21:30

Siddharth Rout

150k18 gold badges212 silver badges258 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Amatya Over a year ago

This is a small portion of a very long series of data processing that I am doing. My boss wants me to create a button that will enable him to upload his monthly file and generate all kinds of stuff with one click. I am brand new to excel vba and you will see every step of the project on here as I struggle with stuff. :)

Siddharth Rout Over a year ago

It will be approximately 10-15 lines of code (minus the declarations).

Amatya Over a year ago

neat method though! Thanks!!

Siddharth Rout Over a year ago

@Amatya: Amended my answer It will be 9 lines of code (including the declarations)

Amatya Over a year ago

In Mathematica coding, they have 1 line coding competitions. I think you're a future champion. :)

Collectives™ on Stack Overflow

Excel VBA AutoFilter Array error

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related