Solved: problem with array loop

[CatDaddy]

[VBA]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[/VBA]

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?

[mohanvijay]

Try this

[vba]

Dim cell As Range
Dim lr As Long, x1 As Long, x2 As Long, i As Long
Dim grpArr As Variant
'START GROUPINGS
lr = Range("C65536").End(xlUp).Row

Dim Str_RW As String

For Each cell In Range("A2:A" & (lr - 1))
If cell.Value = "" Or cell.Value = "GROUP_NAME" Then
Str_RW = Str_RW & cell.Row & ","
End If
Next cell

Str_RW = Left(Str_RW, Len(Str_RW) - 1)

grpArr = Split(Str_RW, ",")

For i = 0 To UBound(grpArr)
''
Next i

[/vba]

[CatDaddy]

trying to just skip the array all together:

[VBA]Sub groupings2()
Dim cell As Range, cell2 As Range
Dim lr As Long, x1 As Long, x2 As Long, i As Long

'START GROUPINGS
lr = Range("C65536").End(xlUp).Row
MsgBox (lr)
Rows(2 & ":" & (lr - 1)).Rows.Group
For Each cell In Range("A1:A" & (lr - 1))
If cell.Value = "" Or InStr(cell.Text, "GROUP_NAME") Then

x1 = cell.Row

For Each cell2 In Range("A" & x1 & ":A" & (lr - 1))
If cell2.Value = "" Then
x2 = cell2.Row
Exit For
End If
Next cell2

End If

Rows((x1 + 1) & ":" & (x2 - 1)).Rows.Group

Next cell
End Sub[/VBA]


the code from the macro recorder was very simple:

[VBA]Rows("2:50").Select
Selection.Rows.Group
Rows("2:2").Select
Selection.Rows.Group
Rows("4:8").Select
Selection.Rows.Group
Rows("10:14").Select
Selection.Rows.Group
Rows("16:21").Select
Selection.Rows.Group
'...[/VBA]

[CatDaddy]

trying mohanvijay's suggestions throws type mismatch error at line:

grpArr = Split(Str_RW, ",")

[CatDaddy]

[VBA]Sub groupings()
Dim cell As Range
Dim lr As Long, x1 As Long, x2 As Long, i As Long
Dim grpArr() As String
Dim Str_RW As String
'START GROUPINGS
lr = Range("C65536").End(xlUp).Row
Str_RW = "1,"
For Each cell In Range("A2:A" & (lr - 1))
If cell.Value = "" Or InStr(cell.Text, "GROUP") Then
Str_RW = Str_RW & cell.Row & ","
End If
Next cell

Str_RW = Left(Str_RW, Len(Str_RW) - 1)

grpArr = Split(Str_RW, ",")
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[/VBA]

SUCCESS!!!! Thank you mohanvijay

[CatDaddy]

point of information for any of the super geniuses out there...do array functions in VBA just not work very well or did i do something stupid (initially)?