1

I have the following VBA script, which was simply built to delete the entire row, if a value in Col T is 2. The problem is my dataset is usually large (200k lines +). Is there any way I can speed up this process, or write the script better? The Sheet name is "NonSerial".

Sub DeleteRows()

Dim lr As Long, lr2 As Long

Application.ScreenUpdating = False


lr = Cells(Rows.Count, "T").End(xlUp).Row


Columns("T:T").AutoFilter
ActiveSheet.Range("$T$1:$T$" & lr).AutoFilter Field:=1, Criteria1:="2"


lr2 = Cells(Rows.Count, "T").End(xlUp).Row


If lr2 = 2 Then Exit Sub


Application.DisplayAlerts = False
ActiveSheet.UsedRange.Offset(1, 0).Resize(ActiveSheet.UsedRange.Rows.Count - 1).Rows.Delete
Application.DisplayAlerts = True


Range("T1").AutoFilter

Application.ScreenUpdating = True

End Sub
Gray Meiring
  • 297
  • 2
  • 3
  • 16
  • 1
    Does this answer your question? [Faster Or Best Alternative for VBA to delete rows not containing specific values?](https://stackoverflow.com/questions/72382484/faster-or-best-alternative-for-vba-to-delete-rows-not-containing-specific-values) – Sgdva Jun 24 '22 at 13:39
  • I think it's difficult to delete row (contains certain value) from about 200K rows without takes a long time. Even using the replace function - no filtering - no looping, in my side **it took almost 1 minute** to delete rows with certain value (total row to be deleted is 55K something) from my 266K rows of data. So long. – karma Jun 24 '22 at 15:03
  • @karma commented question speeds up this process, I stressed it for 100k rows and took nearly a second. It is my "go" now for deleting rows/columns – Sgdva Jun 24 '22 at 15:57
  • @Sgdva, you are correct (as also the link you provided in your comment), although the replace (to boolean) function is still takes time but it shorter then using the filter code. And it also my "go" whenever the replace (to boolean) function can be applied. For another example, I use it to find all the cells which contain certain text because the find function need to loop with findnext then union the found cell. – karma Jun 24 '22 at 16:23

0 Answers0