Forum Discussion
Excel bulky worksheet
In my excel file one sheet has become very bulky. There are no conditional formattings, no dynamic formulae. The visible data is also just 10-12 rows in that sheet. I have one or two conky columns in the left and one or two calculation columns in the right side of the data. I have to paste my deals from other workbook to this workbook on daily basis. And some other sheets get data from this bulky worksheet for other calculations. Therefore I cannot just delete this sheet altogether and create new worksheet.
I have tried deleting all rows after my data ends ie from 13th row to last row.
Even when I navigate from one sheet to other sheet besides this worksheet the file gets hanged for 4-5 minutes. The overall file size right now is 6mb binary file when I compare to same file 4-5 months ago it was just 2 mb binary. Not a single sheet data is more than 1000 rows. My guess is this problem is because of filtered data copy pasting from one file to this file ie because of using filters.
Can I get a solution to fix this problem
Currently I create a new worksheet create my formula and conky columns with formulae paste the fresh deals data from main source, change all links from bulky sheet to new sheet and then delete bulky worksheet. But because of this bulky sheet excel hangs everytime I navigate so it takes at least an hour to do this exercise and reduce the file size. I wanted to know if there is any other simple solution excel way and vba method if any to get the bulky sheet cleared
Here's how to resolve it.
1. Open the workbook but don't go to the "Interest Deal" sheet
2. Press Alt+F11 to open the Visual Basic Editor
3. In VB Editor, go to view and show Immediate Window (it will show at the bottom).
4. Enter the following in the immediate window and then press enter:
Sheets("Interest Deal").pictures.delete
Immediate window looks like this (it's whited out because it's taking a while):
5. Go to lunch or dinner and then come back. You have 68,000 pictures in that sheet. It will take a while to remove those.
9 Replies
- https://1drv.ms/x/s!AqGXCNtqu1XwkUvRSIr1uOSuXM9y?e=0xty0a
Uploaded the file in my onedrive
Here is the link please give me some solution both deal sheets are an issueHere's how to resolve it.
1. Open the workbook but don't go to the "Interest Deal" sheet
2. Press Alt+F11 to open the Visual Basic Editor
3. In VB Editor, go to view and show Immediate Window (it will show at the bottom).
4. Enter the following in the immediate window and then press enter:
Sheets("Interest Deal").pictures.delete
Immediate window looks like this (it's whited out because it's taking a while):
5. Go to lunch or dinner and then come back. You have 68,000 pictures in that sheet. It will take a while to remove those.
- Thanks a lot. It worked like a magic.
- I get an OneDrive error.
Try this:
And the web is full of answer why a workbook is slow. Go and search.
- Not working for my file. I have searched 100s of YouTube videos, searched web and also used chatgpt for my problem but I have not received any satisfactory answer. I am struggling for this issue for last 2 years. As a last resort I am posting my issue here not as the first. It's work related classified data in that file otherwise I could have shared those files here also.
What if you clear all data (and metadata) out of the workbook and then upload here so we may take a look? I suspect the problem is not the data but could be an excessive amount of objects (not visible to the eye) or an exorbitant amount of styles.
