☰

Excel 使用的范围有整个列

tag-icon • tag-icon microsoft-excel vba
Excel 使用的范围有整个列

我处理许多 Excel 文件，经常会遇到工作簿相对于其所含数据而言过大，或工作表滚动时速度非常慢的情况。在此过程中，我了解到这通常是由于 过大造成的UsedRange，这是我在这些情况下首先检查的事情之一。

如果我发现工作表确实很大UsedRange（按 ctrl+end），我会尝试一些方法。我总是通过选择单元格 A1、保存工作簿，然后关闭 Excel 来结束每个选项。然后希望看到（在文件管理器中）文件大小确实减小了。如果没有，我会再次打开文件，确认使用的范围确实和以前一样大，然后尝试列表中的下一个方法。

以下是我尝试的方法：

  • 我选择未使用的整行（或整列，或两者）部分，然后右键单击 -> 删除。在 95% 的情况下都有效。
  • 如果这不起作用，我会加倍努力并“清除所有”（主页选项卡->清除->清除全部）已使用的行，并从整个工作表中删除所有条件格式，然后再次删除未使用的行。
  • 如果仍然不成功，我会转到 VBA，ActiveSheet.UsedRange在即时窗口中强制“重新计算”使用的范围。如果这不起作用，我会再试一次，但要删除未使用的行和/或列。

现在我手上有一本工作簿，即使使用最后一个技巧，它的大小也没有减小。使用的行数过多（即：全部）。我VBA显然注意到了这一点整列被使用，就像ActiveSheet.UsedRange.Address价值一样$A:$EM-没有行号！

这对我来说确实是新鲜事，所以我尝试了一些更极端的事情：

  • 我从工作表中删除了所有格式（单击左上角，然后单击主页选项卡 -> 清除 -> 清除格式），以及超链接、注释和说明（从同一下拉菜单中）。然后删除所有未使用的行。
  • 我做了同样的事情，但是选择了整个工作表的“普通”样式并取消合并所有单元格。
  • 我已经解冻了窗格（我对这里的情况很绝望）。

该工作表现在看起来更像是一个文本文件而不是工作表，但它仍然有 3MB 大（42 x 142 个单元格），并且根据 Excel（和 VBA）使用的范围仍然相同（或几乎相同，现在相同$A:$EL）。

我还能做其他什么事吗？

非常感谢！

PS - 我使用的是 Office 365 ProPlus，Build 12430.20184 即点即用版本

另外两条可能值得关注的评论：

A) 我确信问题确实出在这张表上：

  • 我已从文件中删除了所有其他工作表。（我检查了非常隐藏工作表。
  • 我已删除工作簿中的所有命名范围。
  • 工作簿中没有附加任何 VBA 模块或表单。
  • 而且，最令人信服的是：如果我添加一个包含大致相同数量数据的新工作表，并删除有问题的工作表，文件大小就会下降到几十kB。

B）另外，我要提一下做工作：选择我认为使用过的范围，复制它，然后将其粘贴到新的（空白）工作表上。但是，这有其自身的缺点，使我不愿意使用它。也就是说，所有引用都必须转移到新工作表。这包括（但可能不限于...（这是主要问题））：

  • 任何对其他工作表单元格的引用。可以通过对“original_worksheet!”进行“搜索并替换”为“new_worksheet!”找到这些引用；
  • 公式中命名范围的任何用途；
  • 任何在条件格式中的使用；
  • 在 VBA 代码中使用，取决于工作表的引用方式。

此外，原始工作表上的任何对象（如图表、控件元素（下拉框等），以及最重要的表格）都将被重命名，因此上述几点也适用于这些对象。简而言之，如果可以避免，我不想走这条路，因为工作量很大，而且我担心找不到所有需要更改的引用，从而在删除原始工作表时搞砸我的工作簿。此外，我很好奇，并且（现在）投入精力寻找实际原因。

答案1

尝试创建一个新的空白表，仅选择包含内容的单元格（即，不要全选），将其粘贴到新表中，然后删除旧表，并根据需要重命名新表以与旧表匹配

答案2

我花了几个小时来解决这个问题。所有手动技巧 - 选择、删除、使用“清除格式”菜单命令、删除行、使用 VBA 删除行、告诉 VBA 重新计算 UsedRange 都失败了。文件不会变小，UsedRange 始终是最大（百万）行。

关键是——当你在 VBA 中打印 UsedRange.Address 时，没有行号！

? activesheet.usedrange.address
$A:$AH

因此，UsedRange 中没有指定行的子集。有一些东西强制将范围设置为整个工作表。在我的例子中，那个东西是 RowHeight。

这对我有用。打开 VBA (Alt-F11) 并使用立即模式窗口。一次输入以下内容：

? activesheet.name
[shows sheet name]
? activesheet.usedrange.address
$A:$AH
activesheet.usedrange.rowheight = 23
? activesheet.usedrange.address
$A$11:$AH$1493

普遍改变行高不仅可以使 UsedRange 变小，而且它还会作为更改的一部分自动重新计算 UsedRange！

宾果，文件现在小了 2.5MB。

相关内容