Hey Leute,
ich versuche momentan auf der Arbeit ein Problem in einem Excel Makro zu finden, leider
kenne ich mich selber kaum aus und bin also auf eure Hilfe angewiesen. Das Ziel ist es die Zeilen je nach Einzugslevel zu gruppieren.
Hier mal beispielhaft der Aufbau vom Excelsheet:
Einheit A A 0
Einheit A B 0
Einheit A C 0
Einheit A D 0
Der Code sieht so aus:
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
Vielen Dank für alle hilfreichen Tipps
|