vba 如何删除多个空白的“Microsoft Excel 对象”

声明:本页面是StackOverFlow热门问题的中英对照翻译,遵循CC BY-SA 4.0协议,如果您需要使用它,必须同样遵循CC BY-SA许可,注明原文地址和作者信息,同时你必须将它归于原作者(不是我):StackOverFlow 原文地址: http://stackoverflow.com/questions/10016464/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me): StackOverFlow

提示:将鼠标放在中文语句上可以显示对应的英文。显示中英文
时间:2020-09-11 15:44:27  来源:igfitidea点击:

How to Delete Multiple Blank "Microsoft Excel Objects"

excel-vbaexcel-2003vbaexcel

提问by Phil Downey

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.

我有一个电子表格,其中有一张名为“Sheet1”的工作表。我已取消隐藏所有其他工作表并将其删除。当我打开 VBA 编辑器时,在“Microsoft Excel 对象”下我列出了近 4000 个工作表,我无法通过右键单击删除它们。

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.

我相信当“Sheet1”中没有数据时,这会导致文件的大小太大(~6 MB)。我曾尝试使用“goto>special>objects”方法,但没有以这种方式找到对象。

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

关于如何清除所有这些空对象的任何想法?

VBA Explorer

VBA 资源管理器

回答by Raquel S.

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.

我想我找到了一个解决方案,至少它对我有用,而且我对 VBA 不是很精通。添加属性窗口(在视图下),选择每个工作表,并将可见选项更改为 -1 - xlsheetvisible,它将使其出现在 Excel 中,然后您可以删除选项卡。

回答by Siddharth Rout

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"

有很多方法可以实现你想要的。最快的方法是右键单击Sheet1,然后单击“移动或复制”~~>“新书”~~>“确定”

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.

不幸的是,这不起作用 - 我们在其他工作表中有一些非常冗长的公式,有这个确切的问题,Excel 只会复制它们的前 255 个字符。此外,不会复制原始工作表中的 VBA 代码,这涉及重建受此问题影响的每个电子表格的额外步骤。

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

在此处输入图片说明