1

I am trying to build a code that could filter and delete some 35,000 rows.

I used the below codes but it is taking lot of time(more than 10 mins), can anyone please help me to make it quicker?

Code 1 : 

Range("$A$1:$S$50000").AutoFilter Field:=19, Criteria1:="Delete"
ActiveSheet.AutoFilter.Range.Offset(1, 0).Rows.SpecialCells(xlCellTypeVisible).Delete (xlShiftUp)

Code 2 : 

Dim RngToDelete As Range
Range("$A$1:$S$50000").AutoFilter Field:=19, Criteria1:="Delete"
Set RngToDelete = Selection.SpecialCells(xlCellTypeVisible)
RngToDelete.Delete

Thanks, Amith

Amith.B
  • 27
  • 1
  • 5
  • Possible duplicate of [Excel VBA Performance - 1 million rows - Delete rows containing a value, in less than 1 min](http://stackoverflow.com/questions/30959315/excel-vba-performance-1-million-rows-delete-rows-containing-a-value-in-less) – Rémi Oct 14 '16 at 13:58
  • did you already `application.screenupdating = FALSE`? That will save a ton of time. – Amit Kohli Oct 14 '16 at 14:10
  • Yes i had used - application.screenupdating = FALSE – Amith.B Oct 14 '16 at 17:03

1 Answers1

0

You can accomplish the task by using the AdvancedFilter with much faster performance.

A. Solution without VBA

Data->Advanced Filter, select 'Filter the list in-place' and fill in the data range and criteria.

B. Solution With VBA Code

Range("A1:S50000").AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:=Range("U1:U2"), Unique:=False

Do define the criteria with the fieldname (Column 19) on Cell U1 and '<>Delete' on cell U2. Sure, you can always place your criteria range elsewhere.

AlbertLau14100
  • 96
  • 1
  • 6