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?