Collectives™ on Stack Overflow

Find centralized, trusted content and collaborate around the technologies you use most.

Teams

Q&A for work

Connect and share knowledge within a single location that is structured and easy to search.

I'm currently trying to fix an Excel VBA macro. The final goal is to group the list by indent level.

The structure is similar to this:

Row 1....Row2..Row3

Einheit-----A -40

Einheit------B -20

Einheit------C -20

Einheit------D 0

The weird thing is that the macro seems to work until row 409. In row 410 I get the run-time-error 1004. The Indent-level in line 410 is 9. Maybe you guys have an idea.

+++ Okay so I found out that the Runtime error is somehow coherent with the indent-Level. It allways appears after a row with indent level 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
Thanks in advance!
        
Thanks for contributing an answer to Stack Overflow!
Please be sure to answer the question. Provide details and share your research!
But avoid …
Asking for help, clarification, or responding to other answers.
Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.