Deleting rows older than 90 days

trevshand

New Member
Joined
Dec 11, 2010
Messages
1
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-com:eek:ffice:eek:ffice" /><o:p></o:p>
<o:p> </o:p>
For Each c in Range("g1:g1500000")<o:p></o:p>
If c <= Date - 90 Then c.EntireRow.Delete<o:p></o:p>
Next<o:p></o:p>
<o:p> </o:p>
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
 

Some videos you may like

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)

VoG

Legend
Joined
Jun 19, 2002
Messages
63,650
You need to loop backwards when deleting rows. Try this

Code:
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

MrExcel MVP
Joined
Sep 2, 2009
Messages
16,384
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,123,176
Messages
5,600,146
Members
414,365
Latest member
UUR

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top