0

I'm trying to create an excel macro that deletes rows based on their date. For example, if the date < 02/15/18 - then delete the entire row. I wanted to start off small with just deleting the cell before I scale it out to delete the row, so if you can provide how to delete the row too, that'd be very helpful.

When I run the code below with the following dates in columns A1 - A10 02/08/18, 02/09/18, 02/10/18, 02/11/18, 02/12/18, 02/13/18, 02/14/18, 02/15/18, 02/16/18, 02/17/18,

It deletes a few, but leaves 02/09/18, 02/11/2018, 02/13/2018, which these should have been deleted.

It correctly leaves 02/15/18 - 02/17/18

Private Sub CommandButton2_Click()

Dim i As Integer

For i = 1 To 10

If Cells(i,1).Value < DateValue("February 15,2018") Then Cells(i,1).Delete

Next i

End Sub

Does anyone know why this is happening?

Pᴇʜ
  • 56,719
  • 10
  • 49
  • 73
MrTheBard
  • 163
  • 1
  • 2
  • 10
  • 4
    You need to loop backwards through the rows `For i = 10 To 1 Step -1` – cybernetic.nomad Aug 09 '18 at 15:53
  • I tried that as well. I looked at a post here on Stack that asked a similar question and that just deleted all rows. – MrTheBard Aug 09 '18 at 17:13
  • 1
    deleting rows inside a loop (one by one) will be slow. The best approach is to loop thru the rows (use `application.union`), save the rows that should be deleted as a range and then delete the whole range (use `range.entirerow.delete` – Ibo Aug 09 '18 at 17:54

0 Answers0