Fastest way to delete rows which cannot be grabbed with SpecialCells

by

One efficient solution is to tag all the rows to keep and move all the rows to delete at the end by sorting the tags.
This way, the complexity doesn’t increase with the number of rows to delete.

This example deletes in less than a second, for 50000 rows, all the rows where column I is equal to 2:

Sub DeleteMatchingRows()
    Dim rgTable As Range, rgTags As Range, data(), tags(), count&, r&

    ' load the data in an array
    Set rgTable = ActiveSheet.UsedRange
    data = rgTable.Value

    ' tag all the rows to keep with the row number. Leave empty otherwise.
    ReDim tags(1 To UBound(data), 1 To 1)
    tags(1, 1) = 1  ' keep the header
    For r = 2 To UBound(data)
      If data(r, 9) <> 2 Then tags(r, 1) = r  ' if column I <> 2 keep the row
    Next

    ' insert the tags in the last column on the right
    Set rgTags = rgTable.Columns(rgTable.Columns.count + 1)
    rgTags.Value = tags

    ' sort the rows on the tags which will move the rows to delete at the end
    Union(rgTable, rgTags).Sort key1:=rgTags, Orientation:=xlTopToBottom, Header:=xlYes
    count = rgTags.End(xlDown).Row

    ' delete the tags on the right and the rows that weren't tagged
    rgTags.EntireColumn.Delete
    rgTable.Resize(UBound(data) - count + 1).Offset(count).EntireRow.Delete
End Sub

Note that it doesn’t alter the order of the rows.

More Related Contents:

  1. How to find the days difference between two dates [closed]
  2. Excel matching based on name date and copying data to another sheet
  3. Get all dates between 2 dates in vba
  4. Different languages issue when inserting formula from VBA
  5. Unprotect VBProject from VB code
  6. Can VBA Reach Across Instances of Excel?
  7. Excel VBA – Delete empty rows
  8. Efficient way to delete entire row if cell doesn’t contain ‘@’ [duplicate]
  9. Building Undo Into an Excel VBA Macro
  10. Excel vba add code to sheet module programmatically
  11. Best workaround for VBA Transpose array length limit?
  12. How to use Events with Option Button Controls on Userform [duplicate]
  13. Vba Excel do vlookup from a closed file
  14. excel hyperlink to nothing
  15. How to add headers to a multicolumn listbox in an Excel userform using VBA
  16. How to send files via HTTP_POST with Excel using VBA?
  17. VBA to open Excel hyperlink does not work when hyperlink generated with a formula
  18. How to Add Date Picker To VBA UserForm
  19. Vba macro to copy row from table if value in table meets condition
  20. Difference between Thisworkbook.name and Activeworkbook.name in VBA
  21. Set a cell value from a function
  22. What are the differences between using the New keyword and calling CreateObject in Excel VBA?
  23. Excel VBA wildcard search
  24. Calling regasm without administrative rights for COM interop in Excel VBA
  25. Is there a way for DIR(path) in VBA to handle strings longer than 260?
  26. How do I activate a specific workbook and a specific sheet?
  27. Start VBA macro when editing a cell
  28. VBA Run-time error 438 appears when “paste” runs
  29. If Not function proceeds when value *is* the one specified
  30. Copy an entire worksheet to a new worksheet in Excel 2010

Leave a Comment