1

I'm currently working on a VBA script for deleting rows on an excel spreadsheet with a filter. I've been looking at the following thread and it has give me a good understanding as to how it should work, but I'm struggling to implement it into my own spreadsheet.

Excel VBA Performance - 1 million rows - Delete rows containing a value, in less than 1 min

The Idea of my macro is that it Filters Column 4 by everything that does not contain 'N5' and deletes them.

If anyone could help me write this, it would be greatly appreciated.

--

EDIT: The current formula does work, but it takes approximately 5-7 minutes to run. I need a way to reduce this time as much as possible.

Thank you

    Sub LinesideFilter()

    Dim t#, crit As Range, data As Range, ws As Worksheet
    Dim r&, fc As Range, lc As Range, fr1 As Range, fr2 As Range
    FastWB True
    t = Timer


Sheets("Data").Activate
Range("A2").Select
LastRow = Cells(Rows.Count, 1).End(xlUp).Row


    Range("A1:M" & LastRow).Select
    Selection.AutoFilter
    ActiveSheet.Range("$A$1:$M$" & LastRow).AutoFilter Field:=4, Criteria1:="<>n5*" ', Operator:=xlAnd
    'Rows("2:2").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Delete Shift:=xlUp
    ActiveSheet.ShowAllData

    FastWB False

End Sub
Craig Wheeler
  • 53
  • 3
  • what isn't working with your code? If it just about making it faster then it may be a candidate for the code review site. – QHarr Mar 12 '18 at 09:53
  • 2
    The idea of the post (that you have a link in your post) is to do the opposite. I actually used it in my code, and it does work faster. It takes much longer to delete than copy, so you need to filter the values you want to keep, then copy the entire Filtered range to a new sheet, and then just delete your entire current sheet. Makes sense ? – Shai Rado Mar 12 '18 at 09:56
  • I've amended it to do this for now. I've copy and pasted to a different sheet. I'm just wondering how to do it on one sheet. Thank you – Craig Wheeler Mar 12 '18 at 11:23
  • @CraigWheeler what do you mean in 1 sheet? using this method you need to copy from your original sheet to a new sheet, then delete the original sheet - it's just the way this concept works. – Shai Rado Mar 12 '18 at 11:29
  • @ShaiRado the idea was it actually go through the records I didn't want and delete them, similar to the code used on the link above. I've left it as a copy and paste for now which seems to be working fine. Thank you for your help – Craig Wheeler Mar 12 '18 at 13:15

0 Answers0