Power By 专否社区

How to Delete Multiple Blank "Microsoft Excel Objects"

Go To StackoverFlow.com

1

I have a spreadsheet with one sheet named "Sheet1". I have unhidden all other sheets and removed them. When I open the VBA editor, under "Microsoft Excel Objects" I have nearly 4000 Sheets listed, and I cannot remove them via right click.

It is my belief that this is causing the file's size to be way too large (~6 MB) when there is no data held in "Sheet1". I have tried using the "goto>special>objects" method, and no objects are found this way.

Any ideas on how to clear out all these empty objects?

VBA Explorer

2012-04-04 17:41
by Phil Downey
Sid is as usual correct but I am curious how you got a workbook with 4,000 worksheets - Tony Dallimore 2012-04-04 18:36
I'd like to know too? I've seen some other reports of it occurring in my research, but I'm still confused as to how it actually happened. I'm not responsible for the sheet's usage, just trying to fix this particular issue - Phil Downey 2012-04-05 14:45
Do you have Google Desktop installed - Siddharth Rout 2012-04-05 15:42
No I don't. How would that affect things - Phil Downey 2012-04-05 16:29

2

I think I found a solution, at least it's working for me and I'm not very savvy with VBA. Add the Properties Window (under view), select each sheet, and change the Visible option to -1 - xlsheetvisible, it will make it appear in the excel and then you can delete the tab.

2012-08-29 13:30
by Raquel S.

1

There are many ways to achieve what you want. The fastest way is right click on Sheet1 and click on "Move or Copy" ~~> "New Book" ~~> "OK"

Now you can save the new workbook and delete the old one :)

FOLLOWUP

Unfortunately this won't work - we have some pretty lengthy formulas in other sheets with this exact problem and Excel will only copy the first 255 characters of them. Additionally, the VBA code in the original sheet is not copied, involving additional steps to rebuild each spreadsheet affected with this problem.

In such a case try this code

Option Explicit

Sub Sample()
    Dim ws As Worksheet

    Application.ScreenUpdating = False
    Application.DisplayAlerts = False

    For Each ws In ThisWorkbook.Sheets
        If ws.Name <> "Sheet1" Then ws.Delete
    Next

    Application.ScreenUpdating = True
    Application.DisplayAlerts = True
End Sub

enter image description here

2012-04-04 18:12
by Siddharth Rout
Unfortunately this won't work - we have some pretty lengthy formulas in other sheets with this exact problem and Excel will only copy the first 255 characters of them. Additionally, the VBA code in the original sheet is not copied, involving additional steps to rebuild each spreadsheet affected with this problem - Phil Downey 2012-04-04 19:15
The VBA code in the original sheet IS copied but the VBA code from the module is not. :) May I have a look at the workbook? If yes they you can upload it in wikisend.com and share the link here - Siddharth Rout 2012-04-04 19:17
No sorry, I cannot upload it. You are correct that sheet code would be copied but that the modules were not - Phil Downey 2012-04-04 19:28
I updated the post above. Please check it now - Siddharth Rout 2012-04-04 19:32
Accessing "ThisWorkbook" causes an ActiveX error. I tried accessing the active workbook a different way and the number of sheets in said workbook is only 1 - Phil Downey 2012-04-04 20:23
Did you run the above code from a module? Also can you post a snapshot of the VBA Project Explorer - Siddharth Rout 2012-04-04 20:25
Yes I did. Screenshot has been added in the original post - Phil Downey 2012-04-05 14:47
Ads