CatDaddy

04-05-2012, 10:31 AM

Sub groupings()

Dim cell As Range

Dim lr As Long, x1 As Long, x2 As Long, i As Long

Dim grpArr() As Long

Dim grpD As Boolean

grpD = False

'START GROUPINGS

lr = Range("C65536").End(xlUp).Row

MsgBox (lr)

For Each cell In Range("A2:A" & (lr - 1))

If cell.Value = "" Or cell.Value = "GROUP_NAME" Then

If grpD = True Then

ReDim grpArr(0 To (UBound(grpArr) + 1)) As Long

Else

ReDim grpArr(0 To 0) As Long

grpD = True

End If

grpArr(UBound(grpArr)) = cell.Row

'MsgBox (UBound(grpArr) & ":" & grpArr(UBound(grpArr)))

End If

Next cell

Rows(2 & ":" & (lr - 1)).Rows.Group

For i = 0 To (UBound(grpArr) - 1)

'MsgBox (grpArr(i))

x1 = grpArr(i) + 1

x2 = grpArr(i + 1) - 1

Rows(x1 & ":" & x2).Rows.Group

Next i

End Sub

In for each loop MsgBox shows the right index and value for grpArr, however in the second For loop grpArr(i) gives 0 every time... seems like my array values aren't actually being added to the array?

Dim cell As Range

Dim lr As Long, x1 As Long, x2 As Long, i As Long

Dim grpArr() As Long

Dim grpD As Boolean

grpD = False

'START GROUPINGS

lr = Range("C65536").End(xlUp).Row

MsgBox (lr)

For Each cell In Range("A2:A" & (lr - 1))

If cell.Value = "" Or cell.Value = "GROUP_NAME" Then

If grpD = True Then

ReDim grpArr(0 To (UBound(grpArr) + 1)) As Long

Else

ReDim grpArr(0 To 0) As Long

grpD = True

End If

grpArr(UBound(grpArr)) = cell.Row

'MsgBox (UBound(grpArr) & ":" & grpArr(UBound(grpArr)))

End If

Next cell

Rows(2 & ":" & (lr - 1)).Rows.Group

For i = 0 To (UBound(grpArr) - 1)

'MsgBox (grpArr(i))

x1 = grpArr(i) + 1

x2 = grpArr(i + 1) - 1

Rows(x1 & ":" & x2).Rows.Group

Next i

End Sub

In for each loop MsgBox shows the right index and value for grpArr, however in the second For loop grpArr(i) gives 0 every time... seems like my array values aren't actually being added to the array?