0

I am getting error code while running my Macro:

Run-time error 450: Wrong number of arguments or invalid property assignment

Basically I need to only filter 3 texts XBKK, XBKF and XMAI and I'm unable to do so with below code.

Dim OrigLines, LiveData As Long
Dim FirstRow As Integer

OrigLines = Sheet1.Range("A" & Rows.Count).End(xlUp).Row
FirstRow = 1

    Sheets("Paste_SDR").Select
    Range("$A$1:$FB" & OrigLines).Copy
    Sheets("SDR - working").Select
    Range("A1").Select
    Selection.PasteSpecial
    Rows("1:1").Select
    Application.CutCopyMode = False
    Selection.AutoFilter
    ActiveSheet.Range("$A$1:$FB" & OrigLines).AutoFilter Field:=126, Criteria1:= _
        "<>*XBKK*", Operator:=xlAnd, Criteria2:="<>*XMAI*", Operator:=xlAnd, Criteria2:="<>*XBKF*"

    If Range("A" & Rows.Count).End(xlUp).Row > FirstRow Then
    Range("$A$2:$FB" & OrigLines).SpecialCells(xlCellTypeVisible).Delete Shift:=xlUp
    End If
Pᴇʜ
  • 56,719
  • 10
  • 49
  • 73
Tejas Goswami
  • 7
  • 5

2 Answers2

1

The error is thrown because you cannot pass the same parameter more than once. Anyhow, it's not possible to pass more than 2 criteria to the Autofilter the way you try to do.

If you have a list of values you want to use as filter, pass them as array:

ActiveSheet.Range("$A$1:$FB" & OrigLines).AutoFilter Field:=126, _
    Criteria1:=Array("XBKK", "XBKF", "XMAI"), Operator:=xlFilterValues

Update
The OP wants to delete all rows that don't match these three values. As there is no command SpecialCells(xlCellTypeInvisible), I see the following possibilities

  • Loop over all lines and check if they are visible or not, and collect the invisible rows into a range variable. See for example https://stackoverflow.com/a/39104356/7599798. Use this range for deletion.
  • Create a helper column in the Excel sheet with a formula that gets TRUE or FALSE depending if you want to keep the row. Use something like =OR($DV$2="XBKK", DV$2="XBKF", DV$2="XMAI") and set your filter to this helper column
FunThomas
  • 23,043
  • 3
  • 18
  • 34
  • Hi Tom, Thank you for your quick response. With the solution you provided what is happening is Rows containing "XBKK, XBKF & XMAI" are getting deleted. I need to retain them for later steps. – Tejas Goswami Sep 10 '19 at 12:02
0

I am providing code to remove the unwanted rows. I don't use AutoFilter method, but it will be fast, precise and easy to read.

In short, this code checks each cell for the specified criteria. If it finds values that do not match the specified criteria it places them in a range object. Once all the cells have been checked, those cells placed in the range object will have their rows deleted.

Dim check as Range
For each check in .Range("DV1:DV" & OrigLines) 'loop through cells to check values against (change column reference if needed
    Select Case check.Value 'interpret the value (text) in the cell
        Case "XBKK", "XMAI","XBKF" 'if any of these values, do nothing
        Case Else 'if another value ....
             Dim remove as Range
             If Not remove is Nothing Then 'if remove is already set 
                 Set remove = Union(check, Remove) 'add the cell to the remove range object
             Else 'if remove is not set (this will only occur on the first cell that matches)
                 Set remove = check 'set the remove range object to the matching cell
             End IF
    End Select
Next

If Not remove Is Nothing then remove.EntireRow.Delete 'delete the entire row of every cell in the remove range object
Scott Holtzman
  • 27,099
  • 5
  • 37
  • 72