Deleting rows using VBA
 

I download financial data from a company's website. Some
of the data arrives as follows:

ABC102 120.123 14-01-2005 ABC Fund
ABC102 120.246 17-01-2005 ABC Fund

Because I use vlookup to find "ABC102", the duplicate
data rows foul up the lookup function, so I use a loop in
VBA to find the row with the earlier date (14-01-2005)
then delete that row. There are about 16000 row of data
with about 3000 rows to delete. I cannot sort by date
and eliminate all the 14-01-2005 rows because some are
valid. At first it took a minute or so to run, but
lately it has slowed down to about deleting a row per
second and it takes forever! Does anyone know why is it
now taking so long to delete the row and any ideas how to
speed it up? Is the worksheet running out of rows to
delete? I have a Dell P4 2.66 with 512 meg of RAM.

Thank-you!
Merlin Stewart

Deleting rows using VBA
 

Have you thought about switching to a pivot table. You can then just hold all
16,000 rows and show the highest date or all data from the prior week or
whatever suits your fiendish master plan...

HTH

"Merlin" wrote:

I download financial data from a company's website. Some
of the data arrives as follows:

ABC102 120.123 14-01-2005 ABC Fund
ABC102 120.246 17-01-2005 ABC Fund

Because I use vlookup to find "ABC102", the duplicate
data rows foul up the lookup function, so I use a loop in
VBA to find the row with the earlier date (14-01-2005)
then delete that row. There are about 16000 row of data
with about 3000 rows to delete. I cannot sort by date
and eliminate all the 14-01-2005 rows because some are
valid. At first it took a minute or so to run, but
lately it has slowed down to about deleting a row per
second and it takes forever! Does anyone know why is it
now taking so long to delete the row and any ideas how to
speed it up? Is the worksheet running out of rows to
delete? I have a Dell P4 2.66 with 512 meg of RAM.

Thank-you!
Merlin Stewart

Deleting rows using VBA
 

Hey Lonnie, that worked! Thanks a big heap! I knew
there was some reason for the slowdown, and I didn't want
to complicate things even further. The great thing about
these forums is that you can get different opinions on
issues.

Again, thanks!
Merlin
-----Original Message-----
Merlin, I have experienced the same problem. It seems
that now Excel
tries to calculate vlookup and index/match functions
after deleting
each individual row or something. What I did was:
Application.Calculation = xlCalculationManual and then
turn back
Application.Calculation = xlCalculationAutomatic.
In fact I have now created an add-in with the following
code:

Sub Auto_Open()
Application.OnKey "+^{DELETE}", "DELETEmyRows"
End Sub

Sub DELETEmyRows()
' Keyboard Shortcut: Ctrl+Shift+DELETE
Application.DisplayAlerts = False
Application.Calculation = xlCalculationManual
On Error Resume Next
Selection.EntireRow.Delete
Application.Calculation = xlCalculationAutomatic
Application.DisplayAlerts = True
End Sub

This gives me a keyboard shortcut to delete rows, it
works so much
faster!
Good Luck! Lonnie M.

.