0

I have a large data table where I want to delete the row with the oldest date. The code I use to delete around 900 rows out of 35.000 lasts forever:

Dim sht    As Worksheet
Set sht = Workbooks(ActiveWorkbook.Name).Worksheets("Sheet")
Dim tbl    As ListObject
Set tbl = sht.ListObjects("Table")

' looking for the oldest day
Set c = Range("A1:a" & Rows.count)
date_to_delete = Application.WorksheetFunction.Min(c)

'loop to delete rows
For rw = tbl.DataBodyRange.Rows.Count To 1 Step -1
        If tbl.DataBodyRange(rw, 1) = date_to_delete Then
            tbl.ListRows(rw).Delete
        End If
Next rw

Any idea how to speed this up ? It takes like 5 min now ...

BigBen
  • 46,229
  • 7
  • 24
  • 40
JandB
  • 1
  • 1
  • [Use `Union` and delete at the end, only once](https://stackoverflow.com/questions/59975239/for-loop-not-fully-cycling-in-excel-vba). – BigBen Jan 04 '22 at 19:57
  • To begin with you can turn off application.screenupdating (set to false) to avoid screen being updated for each delete. This could also be done with setting the Autofilter on the range, and EntireRow.delete; could be faster. – tinazmu Jan 04 '22 at 23:07
  • I do have application.screenupdating off, only pasted a part of the code. EntireRow.delete is not and option as I have other stuff next to the Table. Thanks anyway – JandB Jan 06 '22 at 14:42

0 Answers0