Deleting Entire Row in Excel VBA From A Certain Cell Down

Question

I am trying to delete whole rows of data if a date is more than a week later than today, but I don't want to delete the header. Today's date is a variable appearing in A2.

Edit: Column A has dates in the format of dd/mm/yyyy.

This code is what I have at the moment but it doesn't work and erases the header:

Sub deletedates()

Dim firstDate As Date, secondDate As Date
Dim i As Range

firstDate = DateValue(Range("A2"))
secondDate = DateAdd("d", 6, firstDate)

MsgBox secondDate

For Each i In Range("A:A")
If i.Value > secondDate Then
i.Select
ActiveCell.EntireRow.Delete

End If
Next i


End Sub

How many cells do you have? Instead of using `Range("A:A") - can you not use specific range in which the data is input? Also, the loop doesn't work as expected because you are deleting the cells while traversing it. — shahkalpesh
– shahkalpesh, Commented Jun 17, 2013 at 14:30

Skip Intro · Accepted Answer · 2013-06-17 12:06:37Z

1

I've added something that helps two issues:

1) The lngCounter = Cells.Find("*", [A1], , , xlByRows, xlPrevious).row finds the last row of data, as your original code will look at each cell in Column A, which could be over a million if you use newer versions of Excel.

2) I have started the search for data at row 2 of column A to avoid deleting the header row.

Hope this helps!

Sub deletedates()

Dim firstDate As Date, secondDate As Date
Dim i As range
Dim lngCounter As Long
Dim strRange As String

lngCounter = Cells.Find("*", [A1], , , xlByRows, xlPrevious).row 'Find last row of data

strRange = "a2:a" & lngCounter 'Start at Row 2, Column A, finish at last row of Column A

firstDate = DateValue(range("A2"))
secondDate = DateAdd("d", 6, firstDate)

'MsgBox secondDate

    For Each i In range(strRange) 'Starts at second row of data.

        If i.Value > secondDate Then
        i.Select
        ActiveCell.EntireRow.Delete

        End If

    Next i

End Sub

answered Jun 17, 2013 at 12:06

Skip Intro

8607 silver badges12 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

clattenburg cake Over a year ago

THanks SKip Intro, the loop seems to work now as the header isn't deleted now- however it doesn't seem to delete the rows which I need to get rid off? Possibly a problem with my activecell.entirerow.delete command?

Skip Intro Over a year ago

I was using dummy dates data in my test and it seemed to delete the required rows. Could you amend your question to show what your data in column A looks like so I can do a like for like test?

clattenburg cake Over a year ago

It seems I have to run the code several times before I'm finally left with data from the dates I need. Not sure why this is the case...

Collectives™ on Stack Overflow

Deleting Entire Row in Excel VBA From A Certain Cell Down

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related