MS Excel is very very slow in deleting content! Any work around that?

[jensAThome]

I am currently testing some code that was previously running very well in MS Excel 2003 and earlier. However using the same code in Excel 2007 runs very slow. I narrowed it down to this command. Is there a better way to delete content? Or is this a setup problem?

Code:

Sub delete_my_content(sheet_name As String, x, y, dx, dy As Integer)
Dim error_string As String
On Error GoTo errorhandler

Sheets(sheet_name).Select
Sheets(sheet_name).Cells(x, y).Resize(dx, dy).Select
Selection.ClearContents
Exit Sub
errorhandler:

Any suggestions welcome

 

[misterstick]

get rid of the selects.

Sheets(sheet_name).Cells(x, y).Resize(dx, dy).ClearContents

hth,

mr s. <

 

[jensAThome]

Thanks Mr s.,

I can't say that your code is making it any faster. Are you using MS Excel 2007 or previous versions?

Kind regards

Jens

 

[misterstick]

getting rid of selects is generally a good idea. it will speed your code somewhat, although perhaps not noticeably.

as far as 2007 goes i can't help you.


mr s. <

 

[AnotherHiggins]

Avoiding use of Select whenever possible is a good idea.

I don't have 2007. either, but do you have calculations (formulas) that left behind that refer to the data being cleared?

Try wrapping your code as follows and see if it helps at all

Application.ScreenUpdating = False
Application.EnableEvents = False
Application.Calculation = xlCalculationManual
[tab][green]'Your code here[/green]
Application.Calculation = xlCalculationAutomatic
Application.EnableEvents = True
Application.ScreenUpdating = True

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[JensKKK]

John,

Thanks for your suggestions. I tried your code, but it does not make any difference. Excel 2007 still runs very slow. It must be something else.

 

[ck1999]

I tried you code on a worksheet (with no formulas) with a resize of 100x200. Both your code and misterstick works practically instantly with xl 2007.


ck1999

 

[xlbo]

IT'll be something on the spreadheet itself


what else is on teh sheet that you are deleting content from ?

As a general point, yes, selects make code run slower but that is only really apprent in long automation routines. For something like this it should make little or no difference.

Does the speed of code execution increase if you have already interacted with the worksheet? ie. before you run the code, change a font or manually delete a blank row. See if that speeds things up....this is because excel (in general - can;t speak for 2007) can be very slow when it first opens a workbook - often the 1st time you make any changes - even something as simple as bolding a font etc - can take a long time....

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[JensKKK]

I tried you code on a worksheet (with no formulas) with a resize of 100x200. Both your code and misterstick works practically instantly with xl 2007.


ck1999

That is interesting. I have only one computer with ms excel 2007 and that b.. is really slow when deleting content (we are talking here about a couple of minutes for deleting a few thousand cells.

I have noticed with some of the older ms excel version have problems to carry out the delete contents command. On the older version of excel I used the detect and repair function to cure the problem. However I can't find a similar function in ms excel 2007. I have been though all sort of web based diagnostics that are on offer by ms , but they all diagnose excel 2007 as "healthy" although it is very slow in deleting content.

Geoff might be right, but I don't know waht else to do.

 

[ck1999]

if you can attach the spreadsheet and see if it takes other people a longer time than it does you. And that way we can see if there is anything else that may be causing the issue.

Ck1999

 

[AnotherHiggins]

Just a guess - but could it be a compatibility problem where the file is saved as Excel 2003? If, from 2007, you save the file as the latest excel format does that make a difference?

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[BaldyMcFatFat]

I have found that 2007 is slower on some coding. I tested this with some code I had used in Excel 2003 by manually inputting the same code into 2007 and it ran slower. I didn't understand it either and just thought my computer was being stupid. Now, I'm not so sure. If anyone finds any ideas about this, short of an MS Service Pack, I would love to hear them as well.

 

[wotgoesup]

I was having long delays deleting rows from, and when changing data in a large sheet that had mainly conditional lookups. (e.g. =IF(M135=1,S135/(VLOOKUP(Z135,swdctgt,4,0)),0) ). Everytime I changed one cell or deleted/inserted a row, it seemd to take 40 - 50 secs.

In my case, the issue was data copied in from other workbooks - even with a paste special / values. The reason was that Excel also copied over the range names. Once I deleted all the external names (insert / name), it improved dramatically.
Maybe you have copied over ranges from other files?