EXCEL hangs when deleting large blocks of data
 

I have databases that normally have approximately 30 columns and 40,000 rows
of data, but sometimes go up to about 90,000 rows. It's all plain data, no
formulas, no cells linked to this data (except a pivot table in another tab,
that is not set to automatically update).

When I have a monthly update, I delete the previous data and then paste in
the new data. Sometimes, with no apparent pattern, EXCEL hangs when I try to
delete the data. I do this by selecting all the data and hitting the delete
key. I don't want to delete rows or columns because I don't want to mess up
the pivot table settings, which I use to analyze the data each month.

Today, I timed 7 minutes waiting for the screen to refresh and finally used
Task Manager to close EXCEL (not responding). After multiple tries, I was
finally able to delete the old data, 5,000 - 10,000 rows at a time. Each
delete took a long time. I even tried pasting a block of blank cells over the
data - no improvement.

I even rebooted and tried with only EXCEL running. No change. Last month it
worked fine. Since then I had installed WINDOWS Search and Google gadgets. I
uninstalled WINDOWS Search and turned off Google gadgets and rebooted. No
improvement.

Any ideas why this is happening and what i can do?

Answer: EXCEL hangs when deleting large blocks of data
 

It sounds like you are experiencing performance issues when trying to delete large blocks of data in Excel. This can be frustrating, but there are a few things you can try to improve the situation.

1. Check for updates: Make sure that Excel is up to date with the latest updates and patches. Sometimes, performance issues can be resolved with a simple update.
   
2. Clear the clipboard: When you copy and paste large amounts of data, it can fill up the clipboard and slow down Excel. Clearing the clipboard can help improve performance. To do this, open the Home tab, click on the Clipboard group, and then click on the Clear All button.
   
3. Disable add-ins: Add-ins can sometimes cause performance issues in Excel. Try disabling any add-ins that you are not using to see if this improves performance. To do this, go to the File tab, click on Options, and then click on Add-ins. From there, you can manage your add-ins.
   
4. Use the Clear Contents command: Instead of selecting all the data and hitting the delete key, try using the Clear Contents command. This will remove the data from the cells, but leave the formatting intact. To do this, select the cells you want to clear, right-click, and then select Clear Contents.
   
5. Split the data into smaller chunks: If you are still experiencing performance issues, try splitting the data into smaller chunks and deleting them one at a time. This may take longer, but it can help prevent Excel from hanging.
   
6. Use a macro: If you are comfortable with macros, you can create a macro to delete the data for you. This can be faster than manually deleting the data. Here is an example macro that deletes all the data in a worksheet:
   
   
   Formula:

   ---

   Sub DeleteData()
    Cells.Select
    Selection.ClearContents
End Sub 


   ---

   To use this macro, open the Visual Basic Editor (press Alt + F11), create a new module, and then paste in the code. Save the macro and then run it from the Excel worksheet.

I hope these tips help improve the performance of Excel when deleting large blocks of data.

EXCEL hangs when deleting large blocks of data
 

Hi

make sure that Google Search isn't still installed.
Also, turn off Automatic Calculation whilst you Delete, then turn it back
on again afterwards
Formulas tabCalculation optionsManual
--
Regards
Roger Govier

"Oxo" wrote in message
...
I have databases that normally have approximately 30 columns and 40,000
rows
of data, but sometimes go up to about 90,000 rows. It's all plain data, no
formulas, no cells linked to this data (except a pivot table in another
tab,
that is not set to automatically update).

When I have a monthly update, I delete the previous data and then paste in
the new data. Sometimes, with no apparent pattern, EXCEL hangs when I try
to
delete the data. I do this by selecting all the data and hitting the
delete
key. I don't want to delete rows or columns because I don't want to mess
up
the pivot table settings, which I use to analyze the data each month.

Today, I timed 7 minutes waiting for the screen to refresh and finally
used
Task Manager to close EXCEL (not responding). After multiple tries, I was
finally able to delete the old data, 5,000 - 10,000 rows at a time. Each
delete took a long time. I even tried pasting a block of blank cells over
the
data - no improvement.

I even rebooted and tried with only EXCEL running. No change. Last month
it
worked fine. Since then I had installed WINDOWS Search and Google gadgets.
I
uninstalled WINDOWS Search and turned off Google gadgets and rebooted. No
improvement.

Any ideas why this is happening and what i can do?

EXCEL hangs when deleting large blocks of data
 

Oxo wrote:
I have databases that normally have approximately 30 columns and 40,000 rows
of data, but sometimes go up to about 90,000 rows. It's all plain data, no
formulas, no cells linked to this data (except a pivot table in another tab,
that is not set to automatically update).

When I have a monthly update, I delete the previous data and then paste in
the new data. Sometimes, with no apparent pattern, EXCEL hangs when I try to
delete the data. I do this by selecting all the data and hitting the delete
key. I don't want to delete rows or columns because I don't want to mess up
the pivot table settings, which I use to analyze the data each month.

Today, I timed 7 minutes waiting for the screen to refresh and finally used
Task Manager to close EXCEL (not responding). After multiple tries, I was
finally able to delete the old data, 5,000 - 10,000 rows at a time. Each
delete took a long time. I even tried pasting a block of blank cells over the
data - no improvement.

I even rebooted and tried with only EXCEL running. No change. Last month it
worked fine. Since then I had installed WINDOWS Search and Google gadgets. I
uninstalled WINDOWS Search and turned off Google gadgets and rebooted. No
improvement.

Any ideas why this is happening and what i can do?

Excel sometimes gags on large amounts of data. Have you considered using
Access to handle your 2,700,000 (max) data cells?

Bill