How to delete rows in Excel based on criteria using VBA?

by

It is normally much quicker to use AutoFilter rather than loop Ranges

The code below creates a working column, then use a formula to detect delete criteria and then autofilter and delete the result records

The working column puts a formula

=OR(L1="ABC",AA1<>"DEF")
into row 1 of the first blank column then copies down as far ar the true used range. Then any TRUE records are quicklly deleted with AutoFilter

Sub QuickKill()
    Dim rng1 As Range, rng2 As Range, rng3 As Range
    Set rng1 = Cells.Find("*", , xlValues, , xlByColumns, xlPrevious)
    Set rng2 = Cells.Find("*", , xlValues, , xlByRows, xlPrevious)
    Set rng3 = Range(Cells(rng2.Row, rng1.Column), Cells(1, rng1.Column))
    Application.ScreenUpdating = False
    Rows(1).Insert
    With rng3.Offset(-1, 1).Resize(rng3.Rows.Count + 1, 1)
        .FormulaR1C1 = "=OR(RC12=""ABC"",RC27<>""DEF"")"
        .AutoFilter Field:=1, Criteria1:="TRUE"
        .EntireRow.Delete
        On Error Resume Next
        'in case all rows have been deleted
        .EntireColumn.Delete
        On Error GoTo 0
    End With
    Application.ScreenUpdating = True
End Sub

More Related Contents:

  1. Save excel file multiple times with file names in another workbook.
  2. Read/Parse text file line by line in VBA
  3. How do I find the last column with data?
  4. Excel VBA: Answer gets “stuck”
  5. How to insert a picture into Excel at a specified cell position with VBA
  6. Save multiple sheets to .pdf
  7. Having multiple Excel instances launched, how can I get the Application Object for all of them?
  8. filter out multiple criteria using excel vba
  9. Excel VBA autofilter all but three
  10. How can I create a calendar input in VBA Excel?
  11. Cells.Find() Raises “Runtime Error 91: Object Variable or With Block Not Set”
  12. Excel VBA code to copy a specific string to clipboard
  13. Merge values of column B based on common values on column A
  14. How to import a table from web page (with “div class”) to excel?
  15. Excel VBA – Range.Copy transpose paste
  16. How to determine the last Row used in VBA including blank spaces in between [duplicate]
  17. How to give a time delay of less than one second in excel vba?
  18. Changing the date format to yyyy-mm-dd
  19. Delete all duplicate rows Excel vba
  20. In Excel, can I use a hyperlink to run vba macro?
  21. Select Newest Record and Create New Table of Unique Values in Excel
  22. Are ActiveX Controls Disabled?
  23. Macro to export MS Word tables to Excel sheets
  24. Excel VBA – select multiple columns not in sequential order
  25. How to check whether certain sheets exist or not in Excel-VBA? [duplicate]
  26. Application.WorksheetFunction.Match method
  27. Excel Hide/Show all tabs on Ribbon except custom tab
  28. Macros Not Showing Up in Macro Table
  29. Excel telling me my blank cells aren’t blank
  30. Excel VBA Run-time Error ‘32809’ – Trying to Understand it

Leave a Comment