Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro for deleting rows and serialising the remaing rows | Links and Linking in Excel | |||
Macro for deleting rows and serialising the remaing rows | Setting up and Configuration of Excel | |||
Macro for deleting rows and serialising the remaing rows | Excel Worksheet Functions | |||
Help!! I have problem deleting 2500 rows of filtered rows!!!! | Excel Discussion (Misc queries) | |||
deleting hidden rows so i can print only the rows showing?????? | Excel Worksheet Functions |