VBA fast way to delete rows in a 2-column range if the date is less than a defined date

[RockandGrohl]

Hello everyone,

I've defined a "Ddate" as being "Now()+14"

I have a set of data on "Temp" sheet which has 2 columns. A is string and B is a list of dates.

What I need to do is quickly run down these ~3,000 rows and delete any row where the value in B is less than or equal to "Ddate"

I've tried a Do Until loop, but it's incredibly slow.

Thanks!

 

[bobsan42]

Try
For each cell in range b:b if cell.value = date then set rng =union(rng, cell)
Rng.entirerow.delete
Sorry, but i type this on my phone.

 

[Fluff]

Why not use autofilter & delete the visible rows

 

[offthelip]

try this: using varinat arrays for speed!!

Sub ddt()
lastrow = Cells(Rows.Count, "A").End(xlUp).Row
inarr = Range(Cells(1, 1), Cells(lastrow, 2))
Range(Cells(1, 1), Cells(lastrow, 2)) = ""
outarr = Range(Cells(1, 1), Cells(lastrow, 2))
indi = 1
For i = 1 To lastrow
 If inarr(i, 2) > Now() + 14 Then
   outarr(indi, 1) = inarr(i, 1)
   outarr(indi, 2) = inarr(i, 2)
   indi = indi + 1
 End If
Next i
Range(Cells(1, 1), Cells(lastrow, 2)) = outarr

 

[RockandGrohl]

So sorry guys, I've gotten around this by removing the results I didn't want at the filter stage - initially, I tried this and it couldn't seem to filter the date correctly (cutoff was, an example, 09/05/2019 and later [9th May])

But it was filtering from the 10th September onwards, which wasn't right.

Turns out that even though I was giving a date of "dd/mm/yyyy" and the target sheet to be filtered was "dd/mm/yyyy" that it was instead filtering based on "mm/dd/yyyy" and thus not giving me the right answer.

It's all sorted now, thanks for the help though!

 

[Fluff]

Glad it's sorted & thanks for the feedback