Deleting rows using VBA

[Merlin]

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

 

[Guest]

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

 

[Guest]

Another option and probably a better one would be to Send the data into a
Database such as Access. You can then just run a query to retrieve the max
date and you will never run out of rows to store data... That would be my
favorite option. I never like deleting data because as soon as it is gone
someone comes up with a need for it.

HTH

 

[Guest]

I don't know if you read replies or not but thanks for
the suggestions. Unfortunately I don't have Access on
this computer, and I have have limited experience with
Pivot tables but willing to try it. FYI this data is
deleted and replaced every day, so there's no problem
with sheet capacity. I just can't figure out why it's
taking so much longer now to delete the rows. I'm going
to try modifying the code to delete the old sheet and
create a new one every time and see what happens.

Merlin

 

[Guest]

Play around with Pivot Tables they are really cool and very powerful. Here is
how you could make the pivot table in Excel 2000. I assume that your data has
headings. If not add some. Date, Amount, ...

Place your cursor anywhere in the middle of the data. Select Data ->Pivot
Table. When the Wizard comes up just select finish. All of the appropraite
choices will be made for you. You will now have a new sheet with a table and
a new toolbar. On the toolbar you will see the headings of the data items.
Drag the amounts into the middle and the comapany into the right column. Now
drag the date also into the right column. Right click on the date and select
Field Settings -> Advanced. On the advanced selct Top and change to 1. This
will return the highest date. In Table options you can remove the grand
totals and row totals. In the field setting you can change the subtotals.
Dates are kind of cool in that you can select group and group them by month
year quarter week... You can also autoformat. You can drg the itme around on
the table... Lots of fun stuff to do that will amaze and delight your
friends...

Give it a try...

HTH

 

[Lonnie M.]

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.

 

[Guest]

Merlin-

A stratightforward way to speed things up is to modify your VBA code to mark
the rows (place an "X" in a blank column) to be deleted rather than delete
them one at a time. After all of the "delete" rows have been marked, you can
sort the data and get rid of the rows you don't want. This is not the most
sophisticated solution but it is something that is easy to follow and should
to be significantly faster than what you have now.

-Stan Shoemaker
Palo Alto, CA

 

[Merlin]

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