Deleting rows older than 90 days

[trevshand]

Let me start by saying I am very new to this. This means that a) if you look at my macro and think, "Yeah, that's a bad way of doing it," I won't be offended. Let me know the right way. B) you might need to explain things in a very basic format, as if I'm a slow 3 year old. c) if you have good resource suggestions so I can learn, feel free to pass them along.

So now on to the issue, my fix and my new issue. I have an excel sheet which gets updated from a SQL database. I inherited both. The data tracks interactions and in column G lists the data of the interaction. The problem is, we keep adding interactions, but never deleteing old ones so this excel sheet is now more than 150k rows long.

So we decided we don't need anything older than 90 days. I wrote a macro to delete anything older than 90 days. Here it is:

Sub DeleteCells()<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o> </o>
For Each c in Range("g1:g1500000")<o></o>
If c <= Date - 90 Then c.EntireRow.Delete<o></o>
Next<o></o>
<o> </o>
End Sub

So my first issue is, the macro seems to look at the first row, delete it, which means the secodn row slides up into the first row, then the macro moves to the next row. That basically means I'm getting every other row deleted.

Second, this takes forever, and when I do my data pull, it adds back in all the rows that were just deleted. Is there a faster way to delete these rows in an automated manner?

Third, from the Excel side, is there a way to only pull the data that is 90 old or newer? At this point I am not allowed to make changes to the SQL database.

Any and all thoughts are appreciated.

Trevor

 

[VoG]

You need to loop backwards when deleting rows. Try this

Sub DeleteCells()
Dim LR As Long, I As Long
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
LR = Range("G" & Rows.Count).End(xlUp).Row
For I = LR To 1 Step -1
    If Range("G" & I).Value <= Date - 90 Then Rows(I).Delete
Next I
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
End Sub

 

[AlphaFrog]

If you are allowed to sort the dates in column G, you could have a macro do the sort, find the 1st date that is 90 days old and then delete all rows up to that date in one block. No need to loop. Probably would take about a second or so. Are you allowed to sort by date? If yes, i'll post a macro.

As far as; "is there a way to only pull the data that is 90 old or newer"? It depends how you are pulling the data. Is it a macro or what. Need more details.