运行时错误1004:使用VBA按缩进级别对Excel列表进行分组

Question

我目前正在尝试修复Excel宏。最终目标是将列表按缩进级别分组。

其结构与此类似：

行1....Row2..Row3 艾因海特-A -40 艾因海特-B-20 艾因海特-C-20 Einheit-D0

奇怪的是，宏似乎一直工作到第409行。在410行中，我得到了运行时错误1004。410行的缩进水平是9。也许你们有个主意。

+++，好的，我发现运行时错误与缩进级别是一致的。它总是出现在缩进级别9. +++的行之后。

Sub AutoGroupBOM()
    'Define Variables
    Dim StartCell As Range 'This defines the highest level of assembly, usually 1, and must be the top leftmost cell of concern for outlining, its our starting point for grouping'
    Dim StartRow As Integer 'This defines the starting row to beging grouping, based on the row we define from StartCell'
    Dim LevelCol As Integer 'This is the column that defines the assembly level we're basing our grouping on'
    Dim LastRow As Integer 'This is the last row in the sheet that contains information we're grouping'
    Dim CurrentLevel As Integer 'iterative counter'
    Dim i As Integer
    Dim j As Integer

    Application.ScreenUpdating = False 'Turns off screen updating while running.

    'Prompts user to select the starting row. It MUST be the highest level of assembly and also the top left cell of the range you want to group/outline"
    Set StartCell = Application.InputBox("Select top left cell for highest assembly level", Type:=8)
    StartRow = StartCell.Row
    LevelCol = StartCell.Column
    LastRow = ActiveSheet.UsedRange.Rows.Count

    'Remove any pre-existing outlining on worksheet, or you're gonna have 99 problems and an outline ain't 1
    Cells.ClearOutline

    'Walk down the bom lines and group items until you reach the end of populated cells in the assembly level column
    For i = StartRow To LastRow

        Rows(i).Select
        Level = Cells(i, LevelCol).IndentLevel
        For j = 1 To Level - 1
            Selection.Rows.Group
        Next j
    Next i

    Application.ScreenUpdating = True 'Turns on screen updating when done.

End Sub

提前感谢！

Answer 1

嘿我发现问题出在哪里了。

最大值。Excel中的分组级别为8，因此，当我尝试将第九次分组(缩进级别9)时，我就得到了运行时错误！

Answer 2

Sub GroupByIndent()

    Dim rng As Range, cell As Range
    Dim i As Integer

    Set rng = Range("A6:A880")

    For Each cell In rng

        cell.Select

        For i = 1 To cell.IndentLevel

            Selection.Rows.Group

        Next

    Next cell

End Sub