Macro to delete multiple items using Autofilter

[howard]

I have a macro below to delete several items in Col C

When running the macro I get a run time error 450 "wrong number of arguments or invalid property assignment"


It would be appreciated if someone could assist me

 Sub Delete_unwantedData ()
With Sheets("accounts")
With Sheets("accounts").Cells(1).CurrentRegion
.AutoFilter 3, "922205", xlOr, "", "714105", "714198", "922277"
.Offset(1).SpecialCells(12).EntireRow.Delete
.AutoFilter
End With
   End With

   
End Sub

 

[Fluff]

How about

Sub Delete_unwantedData()
   With Sheets("accounts")
      .Range("A1").AutoFilter 3, Array("922205", "", "714105", "714198", "922277"), xlFilterValues
      .AutoFilter.Range.Offset(1).EntireRow.Delete
      .AutoFilter
   End With
End Sub

 

[howard]

Thanks Fluff

This works perfectly. The range is large +- 700,000 rows show it runs slowly

It may be better to import into power Query and then delete using the filter manually to save time

 

[Fluff]

I know nothing about PQ, so couldn't say.

 

[howard]

I have recently started using it , but going to take some time getting used to it

 

[Peter_SSs]

The range is large +- 700,000 rows show it runs slowly

This should be a lot faster.

Sub Del_Rows()
  Dim a As Variant, b As Variant
  Dim nc As Long, i As Long, k As Long
  
  With Sheets("accounts")
    nc = .Cells.Find(What:="*", LookIn:=xlFormulas, SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column + 1
    a = .Range("C2:C" & .Range("A1").CurrentRegion.Rows.Count).Value
    ReDim b(1 To UBound(a), 1 To 1)
    For i = 1 To UBound(a)
      Select Case a(i, 1)
        Case "922205", "", "714105", "714198", "922277"
          b(i, 1) = 1
          k = k + 1
      End Select
    Next i
    If k > 0 Then
      Application.ScreenUpdating = False
      With .Range("A2").Resize(UBound(a), nc)
        .Columns(nc).Value = b
        .Sort Key1:=.Columns(nc), Order1:=xlAscending, Header:=xlNo
        .Resize(k).EntireRow.Delete
      End With
      Application.ScreenUpdating = True
    End If
  End With
End Sub

 

[howard]

Thanks for the help Peter

I have a workbook, where I have more than 50 codes that I need deleted

Instead of listing these individually in the Array, I would like to list these in Col A on Sheet "Codes to be deleted"

It would be appreciated if you would amend your code to refer to this sheet to delete the rows in Col C on sheet "accounts" containing the items in Col A on Sheet "Codes to be deleted"

 

[Peter_SSs]

It would be appreciated if you would amend your code ...

It would also be appreciated if you gave all the details to start with so we could choose the best method to start with instead of having to go back and change the approach.

Sub Del_Rows_v2()
  Dim a As Variant, b As Variant
  Dim nc As Long, i As Long, k As Long
  Dim DelValues As String
  
  With Sheets("Codes to be deleted")
    DelValues = "|" & Join(Application.Transpose(.Range("A2", .Range("A" & .Rows.Count).End(xlUp))), "|") & "|"
  End With
  With Sheets("accounts")
    nc = .Cells.Find(What:="*", LookIn:=xlFormulas, SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column + 1
    a = .Range("C2:C" & .Range("A1").CurrentRegion.Rows.Count).Value
    ReDim b(1 To UBound(a), 1 To 1)
    For i = 1 To UBound(a)
      If InStr(1, DelValues, "|" & a(i, 1) & "|") > 0 Then
          b(i, 1) = 1
          k = k + 1
      End If
    Next i
    If k > 0 Then
      Application.ScreenUpdating = False
      With .Range("A2").Resize(UBound(a), nc)
        .Columns(nc).Value = b
        .Sort Key1:=.Columns(nc), Order1:=xlAscending, Header:=xlNo
        .Resize(k).EntireRow.Delete
      End With
      Application.ScreenUpdating = True
    End If
  End With
End Sub

 

[howard]

My Apologies Peter. I only realised afterwards that there were many account numbers that needed to be deleted

Many thanks for your help