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?

Improve this question
3
  • 4
    You need to loop backwards through the rows For i = 10 To 1 Step -1 Commented Aug 9, 2018 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. Commented Aug 9, 2018 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 Commented Aug 9, 2018 at 17:54
Related questions
651
How can I avoid using Select in Excel VBA?
1
Excel VBA Deleting Rows of Dates
0
VBA Projects deleting rows
Load 6 more related questions Show fewer related questions

0

Reset to default

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.