vba 以编程方式对行或列进行分组和取消分组
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/14838295/
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
Programmatically group and ungroup rows or columns
提问by assylias
Is there a way to programmatically group / ungroup columns or rows in Excel 2010?
有没有办法以编程方式对 Excel 2010 中的列或行进行分组/取消分组？
Note:
笔记：
- the "manual" command is in Data > Outline > Group / Ungroup
- in excel 2003, this used to work:
someSheet.columns(i).ShowDetail = True / Falsebut it does not work any longer in 2010 for groups (only for pivot tables and subtotals groupings) - recording a macro does not produce any code that I could use
- “手动”命令位于数据 > 大纲 > 分组/取消分组中
- 在 excel 2003 中，这曾经有效：
someSheet.columns(i).ShowDetail = True / False但它在 2010 年不再适用于组（仅适用于数据透视表和小计分组） - 录制宏不会产生任何我可以使用的代码
More precisely, calling myRange.ShowDetail = Truein Excel 2010 does expand a collapsed group, but raises an error if the group is already expanded. And the ShowDetailproperty returns True, whether the group is expanded or not.
更准确地说，myRange.ShowDetail = True在 Excel 2010 中调用确实会展开折叠的组，但如果该组已展开，则会引发错误。并且该ShowDetail属性返回 True，无论组是否展开。
采纳答案by assylias
In Excel 2010, the ShowDetailproperty always return true for a group, whether it is collapsed or expanded. The Hiddenproperty can be used instead:
在 Excel 2010 中，ShowDetail无论组是折叠还是展开，该属性始终为组返回 true。Hidden可以改用该属性：
'to expand
If myRange.EntireColumn.Hidden Then
myRange.EntireColumn.ShowDetail = True
End If
'to collapse
If Not myRange.EntireColumn.Hidden Then
myRange.EntireColumn.ShowDetail = False
End If
回答by Dru
Regarding rows not in pivot tables ... It has NOT been my experience in Excel 2010 that ShowDetail ALWAYS evaluates to True. I thought it did but I didn't realize that I needed to be on the summary row for this property to work as expected. Second of all, I didn't realize the summary row by default is UNDER the grouped rows. Testing for collapsed/expanded became much clearer once I changed that setting to have the summary row above the grouped rows (in the Ribbon: Data > Outline, Show the Outline Dlg Box).
关于不在数据透视表中的行......我在 Excel 2010 中的经验并不是 ShowDetail 总是评估为 True。我认为确实如此，但我没有意识到我需要在摘要行上才能使此属性按预期工作。其次，我没有意识到默认情况下汇总行是在分组行之下。一旦我更改了该设置以使汇总行位于分组行上方（在功能区中：数据 > 大纲，显示大纲 Dlg 框），对折叠/展开的测试变得更加清晰。
If my selected cell is on the summary row, the ShowDetail evalutes to True if the grouped records are showing, and to False if they are not. The key for me was being on the summary row to see that behavior work this way. Having the child/grouped rows above by default really threw me.
如果我选择的单元格在摘要行上，则 ShowDetail 如果分组记录正在显示，则评估为 True，如果没有，则评估为 False。对我来说，关键是在摘要行上看到这种行为是如何运作的。默认情况下，上面的子行/分组行真的让我很失望。
Here's my macro, which dynamically expands and collapses the grouped records tied to the summary row when I select a cell on a summary row. And, it makes my cell in column A bold if the section is expanded. This macro does not run if I've selected more than one cell.
这是我的宏，当我在摘要行上选择一个单元格时，它会动态展开和折叠与摘要行相关联的分组记录。并且，如果该部分被展开，它会使我在 A 列中的单元格变为粗体。如果我选择了多个单元格，则此宏不会运行。
Note that worksheet protection prevents expanding and collapsing groups of cells. My worksheet is protected, so I unprotect the sheets to expand/collapse then reprotect them after. (A possible improvement would be for me to just unprotect/protect just the current sheet instead of all of them.)
请注意，工作表保护可防止扩展和折叠单元格组。我的工作表受到保护，所以我取消保护工作表以展开/折叠然后重新保护它们。（一个可能的改进是对我来说只是取消保护/保护当前工作表而不是所有工作表。）
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
'TOGGLE SHOW/HIDE ROW
If Target.Cells.Count = 1 Then
If (Target.EntireRow.OutlineLevel = 1) And (Target.Offset(1, 0).EntireRow.OutlineLevel = 2) And _
(Target.Column < 15) Then
Call Macros.ProtShts(False)
Target.EntireRow.ShowDetail = Not Target.EntireRow.ShowDetail
If Target.EntireRow.ShowDetail = True Then
Range(Cells(Target.Row, 1), Cells(Target.Row, 14)).Font.Bold = True
Else
Range(Cells(Target.Row, 1), Cells(Target.Row, 14)).Font.Bold = False
End If
Call Macros.ProtShts(True)
End If
End If
End Sub
Remember, I set my summary row to be above the grouped records. If your summary row is below the grouped records (the default) then the offset row reference must be changed to -1, like this:
请记住，我将汇总行设置在分组记录的上方。如果您的汇总行低于分组记录（默认），则偏移行引用必须更改为 -1，如下所示：
(Target.Offset(1, 0).EntireRow.OutlineLevel = 2)
回答by Mark2222
Best is to just change the column width of a cell in the range you hide. This will automatically ungroup the selection.
最好的方法是更改隐藏范围内单元格的列宽。这将自动取消组合选择。
dim wsA as worksheet
set wsA = Worksheets("Name of your Worksheet")
wsA.Columns("A:AJ").Columns.Group
wsA.Range("A:A").ColumnWidth = 22.22
' make the change to one of the cells in the group that you want to unhide.
