☰

在 Excel 中删除行不会重置最后一个单元格（使用范围）

tag-icon • tag-icon microsoft-excel
在 Excel 中删除行不会重置最后一个单元格（使用范围）

我有一个大于 7MB 的 Excel 文件。为了了解权重的来源，我使用了本文中描述的方法SU 问题。

我将大部分权重缩小到一张表，然后执行ctrl+End来检查该表中的最后一个单元格是什么。奇怪的是，该单元格位于表中最后一个可能的行（即第 1048576 行）。

然后，我继续清除所有未使用的行的格式，然后 - 以防万一 - 使用alt+ Shift+↓和alt+ Shift+选择所有空白行（在我的数据下方） →，然后右键单击并删除。

但是，在保存文件、关闭文件并再次打开后，按ctrl+End仍然会将我带到最后一个单元格。

我已经阅读了与手动执行此操作相同的 VBA 内容，似乎那么问题来了指的是同一件事——工作表.使用范围财产。

在我深入研究 VBA 之前 - 我可以手动做些什么来解决这个问题？

PS 在将其标记为重复之前，请注意我在寻找答案时有意用尽了其他选项。

编辑：

澄清一下，我见过这个答案（并尝试过）还有这个知识库文章（也尝试过）——没有雪茄。同样这个最新的答案。

答案1

我感谢您为记录您的努力所付出的额外努力。谢谢。

我经常使用对您不起作用的方法重置臃肿的书籍。我能识别出的两个区别只有两个：

首先，我从来没有尝试过右键单击 -> 删除。我是一个键盘手，所以我一直使用Alt+ E+ D。这应该没什么关系，但奇怪的事情发生了。

第二个不同之处在于，我从未像你一样卡住过，每次保存后都会重置。这很有帮助，对吧？

以下是一些可能的解决方案：

水平/垂直膨胀

今天我遇到了一个问题，我的 VBA 垂直合并了工作表，而我的工作表莫名其妙地水平膨胀了。我最初感到困惑和担心，因为我只遇到了垂直膨胀，并没有想到要检查水平膨胀，直到我尝试重置两次失败。为了完整起见，请顺着我的意思，并防止这种情况发生在您身上：

  1. 首先关闭所有其他 Excel 会话，以便当前工作簿成为唯一打开的工作簿。
  2. 转到右侧的数据并选择第一个空列 - 整列。
  3. Ctrl+ Shift+ →。
  4. Alt+ E，然后D。
  5. Ctrl+ Shift+ End。
  6. Alt+ E，然后D。
  7. Ctrl+Home
  8. 滚动到数据底部
  9. 选择第一个空行-整行。
  10. Ctrl+ Shift+ ↓。
  11. Alt+ E，然后D。
  12. Ctrl+ Shift+ End。
  13. Alt+ E，然后D。
  14. Ctrl+Home
  15. Ctrl+S

是的，这两个Ctrl++Shift行End是多余的，但我认为你可以承受这 15 次击键。

保存工作簿后，您“不应该”需要关闭它，但我并不反对这样做。试一试。如果这不起作用，那么 - 赶紧试试 VBA。

VBA 方式

不要害怕糟糕的 VBA，它是一个很棒的工具，我们所做的事情只需要几秒钟。

  1. 确保可疑工作表处于打开状态且处于活动状态。
  2. Alt+F11打开编辑器。
  3. Ctrl+G打开立即窗口。
  4. 输入?activesheet.usedrange.address并按下Enter。
  5. 如果范围看起来正常（不是如果你的范围过大，那么问题就不在于你的范围。
  6. 如果范围是过大。然后输入activesheet.usedrange并按Enter。请注意，第一个命令使用了问号?，而这个没有。这是因为问号用于我们希望 VBA 显示文本，以便我们可以看到值或状态。如果我们不使用问号，那么我们就是告诉 VBA 执行操作。
  7. 按下后Enter，将光标放在使用 的行上，?然后按下Enter。
  8. 如果范围发生变化，则大功告成。如果范围不变，则问题仍然存在。

非常隐秘的工作表

最后一项任务是寻找隐藏的工作表。不仅仅是隐， 但非常隐蔽（这不是我编造的）。

  1. 项目资源管理器应该已在屏幕左侧打开。如果没有，请按Ctrl+ 。R
  2. 不同 Excel 版本之间的窗口略有不同，但它们都具有相同的展开/折叠树行为。 
  3. 显示的项目数量取决于打开的 Excel 工作簿和插件的数量。如果您关闭了所有其他工作簿，并且显示的项目不止几个，那么您就遇到了其他问题，我们可以稍后详细介绍。
  4. 现在，找到您的项目并展开文件夹以显示工作簿对象（单个工作表和对象ThisWorkbook）。您会认出它们，因为它们会在括号中显示您的工作表名称。
  5. 找到后，使用工作表选项卡查找工作簿中未列出的名称。
  6. 如果找到一个，请在资源管理器窗口中选择它并转到属性窗口（在资源管理器或下方F4）。
  7. 向下滚动找到可见的，它应该是两个值之一：xlhidden或xlveryhidden。
  8. 单击值内部以打开下拉菜单，然后选择xlvisible。
  9. 如果以上都不是，则说明您选择了错误的工作表，甚至可能是错误的项目，请仔细检查您的选择。 
  10. 如果您找到并取消隐藏了纸张，请返回每一张纸张并重置这些范围以查看您的书是否缩小了（不要忘记重新隐藏纸张。

供参考：非常隐蔽的工作表不会在 Excel 中的隐藏/取消隐藏上下文菜单中列出，因此它是保护配置和辅助列的更好方法之一。

  1. 我们已经完成了 VBA，因此请关闭编辑器并返回 Excel。按Ctrl+F3打开名称管理器。
  2. 查找意外的命名范围。除非您非常熟悉工作簿，否则我通常不建议删除命名范围，因为如果您删除它们需要的范围，许多高效的 Excel/VBA 解决方案将无法正常运行；但是，如果您发现某个范围有错误、指向不存在的工作簿、引用未使用的范围或其他明显错误的值 - 那么它们就应该被删除。

其他一些有用的建议

这比我预期的要长得多，所以我会尽量把它总结一下……

  • 检查您的条件格式 - 就像命名范围一样，您正在寻找明显错误或重现的条件。

  • 检查您的查询和数据模型。

  • 确认您没有将图片/图表放置listobjects在难以找到的位置（我们使用名称管理器进行的检查应该可以找到大多数但不是全部）。

  • 确认您的自定义配置设置没有失衡（我注意到同事在主页选项卡上的样式库填充了相同的 20% 重音，展开后显示了数百个。使用 VBA 编辑器，我输入了内容，?activeworkbook.styles.count它返回了 19,000 多个；相比之下，我的有 47 个）。

  • 注意过度的格式——几十万行格式化为货币的数字与几十万行格式化为单元格边框的行是不一样的，单元格边框像迷宫一样，包含不同的颜色和许多不同的自定义数字格式、符号和字体。

  • 检查数据本身，几百行跨越 20 列，每个单元格都填满到极限，这将是一个令人尴尬的 - 但有可能 - 导致膨胀的原因

  • 最后，核选项。将每张工作表单独保存为 CSV 文本文件，然后将其导入到新的工作簿中...除非数据损坏，否则此方法保证有效 - 但重建格式、连接等可能很麻烦。

答案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。

答案3

为我解决这个问题的方法如下（我不记得是在某个 Excel 论坛上找到这个解决方案的）：

  1. 打开一张空白表，查看默认行高是多少。
  2. 返回有问题的工作表并选择您尝试删除的所有行（但在“删除”后仍显示）。选择后，将高度设置为步骤 1 中看到的默认数字。
  3. 再次选择所有行并删除它们，正如 OP 提到的那样。现在，这些未使用的行应该会消失，文件大小应该会显著减小。

答案4

尝试将行高设置为系统默认值（可能因系统而异）。
打开新工作簿，检查行高并应用于性感工作表。
这对我很有帮助，我发现它这里。

相关内容