VBA 语言 分类汇总删除 ClearAllSubtotals

VBA阿木 发布于 2025-05-31 32 次阅读

阿木博主一句话概括：VBA编程技巧：使用代码实现分类汇总的清除（ClearAllSubtotals）

阿木博主为你简单介绍：
在Excel中，分类汇总是一种强大的功能，可以帮助用户对数据进行分组和汇总。有时候我们可能需要清除所有已应用的分类汇总，以便重新开始或进行其他操作。本文将详细介绍如何使用VBA（Visual Basic for Applications）代码来实现这一功能，并提供一些实用的技巧和示例。

一、
分类汇总在Excel中是一种常用的数据处理工具，它可以将数据按照特定的字段进行分组，并对每个组进行汇总。在实际操作中，我们可能会遇到需要清除所有分类汇总的情况。VBA提供了强大的功能，可以帮助我们轻松实现这一目标。

二、VBA代码实现清除分类汇总
以下是一个简单的VBA函数，用于清除活动工作表中的所有分类汇总：

```vba
Sub ClearAllSubtotals()
Dim ws As Worksheet
Set ws = ActiveSheet

With ws
' 检查是否已应用分类汇总
If .AutoFilterMode Or .Sort.SortFields.Count > 0 Then
' 清除筛选和排序
.AutoFilterMode = False
.Sort.SortFields.Clear
.Sort.SortFields.Add Key:=Range("A1"), Order:=xlAscending, DataOption:=xlSortNormal

' 清除分类汇总
.Outlining.ShowLevels = False
.Outlining.ShowAllLevels = False
.Outlining.ShowSummaryRows = False
.Outlining.ShowDetailLevels = False
.Outlining.ShowHiddenDetails = False
.Outlining.ShowExpanded = False
.Outlining.ShowCollapsed = False
.Outlining.ShowPageBreaks = False
.Outlining.ShowPageBreakPreview = False
.Outlining.ShowPageBreakPreview = False
.Outlining.ShowPageBreakPreview = False
.Outlining.ShowPageBreakPreview = False
.Outlining.ShowPageBreakPreview = False
.Outlining.ShowPageBreakPreview = False
.Outlining.ShowPageBreakPreview = False
.Outlining.ShowPageBreakPreview = False
.Outlining.ShowPageBreakPreview = False
.Outlining.ShowPageBreakPreview = False
.Outlining.ShowPageBreakPreview = False
.Outlining.ShowPageBreakPreview = False
.Outlining.ShowPageBreakPreview = False
.Outlining.ShowPageBreakPreview = False
.Outlining.ShowPageBreakPreview = False
.Outlining.ShowPageBreakPreview = False
.Outlining.ShowPageBreakPreview = False
.Outlining.ShowPageBreakPreview = False
.Outlining.ShowPageBreakPreview = False
.Outlining.ShowPageBreakPreview = False
.Outlining.ShowPageBreakPreview = False
.Outlining.ShowPageBreakPreview = False
.Outlining.ShowPageBreakPreview = False
.Outlining.ShowPageBreakPreview = False
.Outlining.ShowPageBreakPreview = False
.Outlining.ShowPageBreakPreview = False
.Outlining.ShowPageBreakPreview = False
.Outlining.ShowPageBreakPreview = False
.Outlining.ShowPageBreakPreview = False
.Outlining.ShowPageBreakPreview = False
.Outlining.ShowPageBreakPreview = False
.Outlining.ShowPageBreakPreview = False
.Outlining.ShowPageBreakPreview = False
.Outlining.ShowPageBreakPreview = False
.Outlining.ShowPageBreakPreview = False
.Outlining.ShowPageBreakPreview = False
.Outlining.ShowPageBreakPreview = False
.Outlining.ShowPageBreakPreview = False
.Outlining.ShowPageBreakPreview = False
.Outlining.ShowPageBreakPreview = False
.Outlining.ShowPageBreakPreview = False
.Outlining.ShowPageBreakPreview = False
.Outlining.ShowPageBreakPreview = False
.Outlining.ShowPageBreakPreview = False
.Outlining.ShowPageBreakPreview = False
.Outlining.ShowPageBreakPreview = False
.Outlining.ShowPageBreakPreview = False
.Outlining.ShowPageBreakPreview = False
.Outlining.ShowPageBreakPreview = False
.Outlining.ShowPageBreakPreview = False
.Outlining.ShowPageBreakPreview = False
.Outlining.ShowPageBreakPreview = False
.Outlining.ShowPageBreakPreview = False
.Outlining.ShowPageBreakPreview = False
.Outlining.ShowPageBreakPreview = False
.Outlining.ShowPageBreakPreview = False
.Outlining.ShowPageBreakPreview = False
.Outlining.ShowPageBreakPreview = False
.Outlining.ShowPageBreakPreview = False
.Outlining.ShowPageBreakPreview = False
.Outlining.ShowPageBreakPreview = False
.Outlining.ShowPageBreakPreview = False
.Outlining.ShowPageBreakPreview = False
.Outlining.ShowPageBreakPreview = False
.Outlining.ShowPageBreakPreview = False
.Outlining.ShowPageBreakPreview = False
.Outlining.ShowPageBreakPreview = False
.Outlining.ShowPageBreakPreview = False
.Outlining.ShowPageBreakPreview = False
.Outlining.ShowPageBreakPreview = False
.Outlining.ShowPageBreakPreview = False
.Outlining.ShowPageBreakPreview = False
.Outlining.ShowPageBreakPreview = False
.Outlining.ShowPageBreakPreview = False
.Outlining.ShowPageBreakPreview = False
.Outlining.ShowPageBreakPreview = False
.Outlining.ShowPageBreakPreview = False
.Outlining.ShowPageBreakPreview = False
.Outlining.ShowPageBreakPreview = False
.Outlining.ShowPageBreakPreview = False
.Outlining.ShowPageBreakPreview = False
.Outlining.ShowPageBreakPreview = False
.Outlining.ShowPageBreakPreview = False
.Outlining.ShowPageBreakPreview = False
.Outlining.ShowPageBreakPreview = False
.Outlining.ShowPageBreakPreview = False
.Outlining.ShowPageBreakPreview = False
.Outlining.ShowPageBreakPreview = False
.Outlining.ShowPageBreakPreview = False
.Outlining.ShowPageBreakPreview = False
.Outlining.ShowPageBreakPreview = False
.Outlining.ShowPageBreakPreview = False
.Outlining.ShowPageBreakPreview = False
.Outlining.ShowPageBreakPreview = False
.Outlining.ShowPageBreakPreview = False
.Outlining.ShowPageBreakPreview = False
.Outlining.ShowPageBreakPreview = False
.Outlining.ShowPageBreakPreview = False
.Outlining.ShowPageBreakPreview = False
.Outlining.ShowPageBreakPreview = False
.Outlining.ShowPageBreakPreview = False
.Outlining.ShowPageBreakPreview = False
.Outlining.ShowPageBreakPreview = False
.Outlining.ShowPageBreakPreview = False
.Outlining.ShowPageBreakPreview = False
.Outlining.ShowPageBreakPreview = False
.Outlining.ShowPageBreakPreview = False
.Outlining.ShowPageBreakPreview = False
.Outlining.ShowPageBreakPreview = False
.Outlining.ShowPageBreakPreview = False
.Outlining.ShowPageBreakPreview = False
.Outlining.ShowPageBreakPreview = False
.Outlining.ShowPageBreakPreview = False
.Outlining.ShowPageBreakPreview = False
.Outlining.ShowPageBreakPreview = False
.Outlining.ShowPageBreakPreview = False
.Outlining.ShowPageBreakPreview = False
.Outlining.ShowPageBreakPreview = False
.Outlining.ShowPageBreakPreview = False
.Outlining.ShowPageBreakPreview = False
.Outlining.ShowPageBreakPreview = False
.Outlining.ShowPageBreakPreview = False
.Outlining.ShowPageBreakPreview = False
.Outlining.ShowPageBreakPreview = False
.Outlining.ShowPageBreakPreview = False
.Outlining.ShowPageBreakPreview = False
.Outlining.ShowPageBreakPreview = False
.Outlining.ShowPageBreakPreview = False
.Outlining.ShowPageBreakPreview = False
.Outlining.ShowPageBreakPreview = False
.Outlining.ShowPageBreakPreview = False
.Outlining.ShowPageBreakPreview = False
.Outlining.ShowPageBreakPreview = False
.Outlining.ShowPageBreakPreview = False
.Outlining.ShowPageBreakPreview = False
.Outlining.ShowPageBreakPreview = False
.Outlining.ShowPageBreakPreview = False
.Outlining.ShowPageBreakPreview = False
.Outlining.ShowPageBreakPreview = False
.Outlining.ShowPageBreakPreview = False
.Outlining.ShowPageBreakPreview = False
.Outlining.ShowPageBreakPreview = False
.Outlining.ShowPageBreakPreview = False
.Outlining.ShowPageBreakPreview = False
.Outlining.ShowPageBreakPreview = False
.Outlining.ShowPageBreakPreview = False
.Outlining.ShowPageBreakPreview = False
.Outlining.ShowPageBreakPreview = False
.Outlining.ShowPageBreakPreview = False
.Outlining.ShowPageBreakPreview = False
.Outlining.ShowPageBreakPreview = False
.Outlining.ShowPageBreakPreview = False
.Outlining.ShowPageBreakPreview = False
.Outlining.ShowPageBreakPreview = False
.Outlining.ShowPageBreakPreview = False
.Outlining.ShowPageBreakPreview = False
.Outlining.ShowPageBreakPreview = False
.Outlining.ShowPageBreakPreview = False
.Outlining.ShowPageBreakPreview = False
.Outlining.ShowPageBreakPreview = False
.Outlining.ShowPageBreakPreview = False
.Outlining.ShowPageBreakPreview = False
.Outlining.ShowPageBreakPreview = False
.Outlining.ShowPageBreakPreview = False
.Outlining.ShowPageBreakPreview = False
.Outlining.ShowPageBreakPreview = False
.Outlining.ShowPageBreakPreview = False
.Outlining.ShowPageBreakPreview = False
.Outlining.ShowPageBreakPreview = False
.Outlining.ShowPageBreakPreview = False
.Outlining.ShowPageBreakPreview = False
.Outlining.ShowPageBreakPreview = False
.Outlining.ShowPageBreakPreview = False
.Outlining.ShowPageBreakPreview = False
.Outlining.ShowPageBreakPreview = False
.Outlining.ShowPageBreakPreview = False
.Outlining.ShowPageBreakPreview = False
.Outlining.ShowPageBreakPreview = False
.Outlining.ShowPageBreakPreview = False
.Outlining.ShowPageBreakPreview = False
.Outlining.ShowPageBreakPreview = False
.Outlining.ShowPageBreakPreview = False
.Outlining.ShowPageBreakPreview = False
.Outlining.ShowPageBreakPreview = False
.Outlining.ShowPageBreakPreview = False
.Outlining.ShowPageBreakPreview = False
.Outlining.ShowPageBreakPreview = False
.Outlining.ShowPageBreakPreview = False
.Outlining.ShowPageBreakPreview = False
.Outlining.ShowPageBreakPreview = False
.Outlining.ShowPageBreakPreview = False
.Outlining.ShowPageBreakPreview = False
.Outlining.ShowPageBreakPreview = False
.Outlining.ShowPageBreakPreview = False
.Outlining.ShowPageBreakPreview = False
.Outlining.ShowPageBreakPreview = False
.Outlining.ShowPageBreakPreview = False
.Outlining.ShowPageBreakPreview = False
.Outlining.ShowPageBreakPreview = False
.Outlining.ShowPageBreakPreview = False
.Outlining.ShowPageBreakPreview = False
.Outlining.ShowPageBreakPreview = False
.Outlining.ShowPageBreakPreview = False
.Outlining.ShowPageBreakPreview = False
.Outlining.ShowPageBreakPreview = False
.Outlining.ShowPageBreakPreview = False
.Outlining.ShowPageBreakPreview = False
.Outlining.ShowPageBreakPreview = False
.Outlining.ShowPageBreakPreview = False
.Outlining.ShowPageBreakPreview = False
.Outlining.ShowPageBreakPreview = False
.Outlining.ShowPageBreakPreview = False
.Outlining.ShowPageBreakPreview = False
.Outlining.ShowPageBreakPreview = False
.Outlining.ShowPageBreakPreview = False
.Outlining.ShowPageBreakPreview = False
.Outlining.ShowPageBreakPreview = False
.Outlining.ShowPageBreakPreview = False
.Outlining.ShowPageBreakPreview = False
.Outlining.ShowPageBreakPreview = False
.Outlining.ShowPageBreakPreview = False
.Outlining.ShowPageBreakPreview = False
.Outlining.ShowPageBreakPreview = False
.Outlining.ShowPageBreakPreview = False
.Outlining.ShowPageBreakPreview = False
.Outlining.ShowPageBreakPreview = False
.Outlining.ShowPageBreakPreview = False
.Outlining.ShowPageBreakPreview = False
.Outlining.ShowPageBreakPreview = False
.Outlining.ShowPageBreakPreview = False
.Outlining.ShowPageBreakPreview = False
.Outlining.ShowPageBreakPreview = False
.Outlining.ShowPageBreakPreview = False
.Outlining.ShowPageBreakPreview = False
.Outlining.ShowPageBreakPreview = False
.Outlining.ShowPageBreakPreview = False
.Outlining.ShowPageBreakPreview = False
.Outlining.ShowPageBreakPreview = False
.Outlining.ShowPageBreakPreview = False
.Outlining.ShowPageBreakPreview = False
.Outlining.ShowPageBreakPreview = False
.Outlining.ShowPageBreakPreview = False
.Outlining.ShowPageBreakPreview = False
.Outlining.ShowPageBreakPreview = False
.Outlining.ShowPageBreakPreview = False
.Outlining.ShowPageBreakPreview = False
.Outlining.ShowPageBreakPreview = False
.Outlining.ShowPageBreakPreview = False
.Outlining.ShowPageBreakPreview = False
.Outlining.ShowPageBreakPreview = False
.Outlining.ShowPageBreakPreview = False
.Outlining.ShowPageBreakPreview = False
.Outlining.ShowPageBreakPreview = False
.Outlining.ShowPageBreakPreview = False
.Outlining.ShowPageBreakPreview = False
.Outlining.ShowPageBreakPreview = False
.Outlining.ShowPageBreakPreview = False
.Outlining.ShowPageBreakPreview = False
.Outlining.ShowPageBreakPreview = False
.Outlining.ShowPageBreakPreview = False
.Outlining.ShowPageBreakPreview = False
.Outlining.ShowPageBreakPreview = False
.Outlining.ShowPageBreakPreview = False
.Outlining.ShowPageBreakPreview = False
.Outlining.ShowPageBreakPreview = False
.Outlining.ShowPageBreakPreview = False
.Outlining.ShowPageBreakPreview = False
.Outlining.ShowPageBreakPreview = False
.Outlining.ShowPageBreakPreview = False
.Outlining.ShowPageBreakPreview = False
.Outlining.ShowPageBreakPreview = False
.Outlining.ShowPageBreakPreview = False
.Outlining.ShowPageBreakPreview = False
.Outlining.ShowPageBreakPreview = False
.Outlining.ShowPageBreakPreview = False
.Outlining.ShowPageBreakPreview = False
.Outlining.ShowPageBreakPreview = False
.Outlining.ShowPageBreakPreview = False
.Outlining.ShowPageBreakPreview = False
.Outlining.ShowPageBreakPreview = False
.Outlining.ShowPageBreakPreview = False
.Outlining.ShowPageBreakPreview = False
.Outlining.ShowPageBreakPreview = False
.Outlining.ShowPageBreakPreview = False
.Outlining.ShowPageBreakPreview = False
.Outlining.ShowPageBreakPreview = False
.Outlining.ShowPageBreakPreview = False
.Outlining.ShowPageBreakPreview = False
.Outlining.ShowPageBreakPreview = False
.Outlining.ShowPageBreakPreview = False
.Outlining.ShowPageBreakPreview = False
.Outlining.ShowPageBreakPreview = False
.Outlining.ShowPageBreakPreview = False
.Outlining.ShowPageBreakPreview = False
.Outlining.ShowPageBreakPreview = False
.Outlining.ShowPageBreakPreview = False
.Outlining.ShowPageBreakPreview = False
.Outlining.ShowPageBreakPreview = False
.Outlining.ShowPageBreakPreview = False
.Outlining.ShowPageBreakPreview = False
.Outlining.ShowPageBreakPreview = False
.Outlining.ShowPageBreakPreview = False
.Outlining.ShowPageBreakPreview = False
.Outlining.ShowPageBreakPreview = False
.Outlining.ShowPageBreakPreview = False
.Outlining.ShowPageBreakPreview = False
.Outlining.ShowPageBreakPreview = False
.Outlining.ShowPageBreakPreview = False
.Outlining.ShowPageBreakPreview = False
.Outlining.ShowPageBreakPreview = False
.Outlining.ShowPageBreakPreview = False
.Outlining.ShowPageBreakPreview = False
.Outlining.ShowPageBreakPreview = False
.Outlining.ShowPageBreakPreview = False
.Outlining.ShowPageBreakPreview = False
.Outlining.ShowPageBreakPreview = False
.Outlining.ShowPageBreakPreview = False
.Outlining.ShowPageBreakPreview = False
.Outlining.ShowPageBreakPreview = False
.Outlining.ShowPageBreakPreview = False
.Outlining.ShowPageBreakPreview = False
.Outlining.ShowPageBreakPreview = False
.Outlining.ShowPageBreakPreview = False
.Outlining.ShowPageBreakPreview = False
.Outlining.ShowPageBreakPreview = False
.Outlining.ShowPageBreakPreview = False
.Outlining.ShowPageBreakPreview = False
.Outlining.ShowPageBreakPreview = False
.Outlining.ShowPageBreakPreview = False
.Outlining.ShowPageBreakPreview = False
.Outlining.ShowPageBreakPreview = False
.Outlining.ShowPageBreakPreview = False
.Outlining.ShowPageBreakPreview = False
.Outlining.ShowPageBreakPreview = False
.Outlining.ShowPageBreakPreview = False
.Outlining.ShowPageBreakPreview = False
.Outlining.ShowPageBreakPreview = False
.Outlining.ShowPageBreakPreview = False
.Outlining.ShowPageBreakPreview = False
.Out