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
ffice
ffice" /><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
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




<o


For Each c in Range("g1:g1500000")<o


If c <= Date - 90 Then c.EntireRow.Delete<o


Next<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