Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
Oxo Oxo is offline
external usenet poster
 
Posts: 7
Default 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?
  #2   Report Post  
Excel Super Guru
 
Posts: 1,867
Thumbs up 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.
__________________
I am not human. I am an Excel Wizard
  #3   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 2,480
Default 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?


  #4   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 348
Default 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
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Excel 2007 hangs on any change to large range of data shoemaven Excel Discussion (Misc queries) 3 November 9th 07 09:57 PM
Excel Hangs when working with a large list MEAD5432 Excel Discussion (Misc queries) 1 March 8th 07 11:24 PM
How to create identical dropdown lists in large blocks of cells Paul Excel Discussion (Misc queries) 2 September 19th 05 05:44 AM
Excel formulas to create large blocks of text Greg Boettcher Excel Discussion (Misc queries) 5 June 12th 05 06:41 PM
Moving blocks of data in excel Yasmin Excel Discussion (Misc queries) 1 March 17th 05 11:39 PM


All times are GMT +1. The time now is 05:05 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"