Rows Group -looping issue

[Jen]

Hi There,

I wish to group of couple of lines at "regular" intervals .... but i fail to
get the "looping" right.
((It seems I cannot just put the "integer"-counter in the Rows("...")-part
of Rows("16:20").Rows.Group))


Anyway the original code I recorded -i removed the select-part- was as
follows:

Sub Macro5()

Rows("16:20").Rows.Group
Rows("26:30").Rows.Group
Rows("36:40").Rows.Group
'....untill end of used range
With ActiveSheet.Outline
.AutomaticStyles = False
.SummaryRow = xlAbove
.SummaryColumn = xlRight
End With
End Sub

What I wish to achieve is that:
Starting from Row10 I group 5 rows up, from 10 rows below Row10,ie Row20
and so on till the endrow in my usedrange.

I hope this makes sense ... ?
Hope you can help me,
Jen

 

[Don Guillett]

from help
Group Method
See AlsoApplies ToExampleSpecifics
Group method as it applies to the ShapeRange object.

Groups the shapes in the specified range. Returns the grouped shapes as a
single Shape object.

expression.Group

expression Required. An expression that returns a ShapeRange object.

Group method as it applies to the Range object.

When the Range object represents a single cell in a PivotTable field’s data
range, the Group method performs numeric or date-based grouping in that
field.

expression.Group(Start, End, By, Periods)

expression Required. An expression that returns a Range object.

Start Optional Variant. The first value to be grouped. If this argument is
omitted or True, the first value in the field is used.

End Optional Variant. The last value to be grouped. If this argument is
omitted or True, the last value in the field is used.

By Optional Variant. If the field is numeric, this argument specifies the
size of each group. If the field is a date, this argument specifies the
number of days in each group if element 4 in the Periods array is True and
all the other elements are False. Otherwise, this argument is ignored. If
this argument is omitted, Microsoft Excel automatically chooses a default
group size.

Periods Optional Variant. An array of Boolean values that specify the
period for the group, as shown in the following table.

Array element Period
1 Seconds
2 Minutes
3 Hours
4 Days
5 Months
6 Quarters
7 Years

If an element in the array is True, a group is created for the corresponding
time; if the element is False, no group is created. If the field isn’t a
date field, this argument is ignored.

Remarks
Because a group of shapes is treated as a single shape, grouping and
ungrouping shapes changes the number of items in the Shapes collection and
changes the index numbers of items that come after the affected items in the
collection.

The Range object must be a single cell in the PivotTable field’s data range.
If you attempt to apply this method to more than one cell, it will fail
(without displaying an error message).

Example
This example groups the field named ORDER_DATE by 10-day periods.

Set pvtTable = Worksheets("Sheet1").Range("A3").PivotTable
Set groupRange = pvtTable.PivotFields("ORDER_DATE").DataRange
groupRange.Cells(1).Group by:=10, _
periods:=Array(False, False, False, _
True, False, False, False)

 

[Guest]

Sub ABCDE()
set rng = ActiveSheet.UsedRange()
For i = 16 To rng(rng.count).row Step 10
Rows(i).Resize(5).Group
Next
ActiveSheet.Outline.ShowLevels RowLevels:=2
End Sub

 

[Jen]

Thanks Tom,

What I hoped it would do & didn't manage myself!

 

[Jen]

Hi Don,

I am trying to use the help as much as possible ... but very often -well I
am doing my first steps in VBA since 1 month- the help is rather useless for
people with my experience-level!
Tom's solution is great ...AND I can understand what it does.
Reading the help has unfortunately not shed much light, hopefully I'll soon
get better at it!
Jen

 

[Jen]

My final result ...
Well, I was grouping "manually" 5000 rows this afternoon ... this goes a bit
more efficiently, thanks again Tom.

Option Explicit
Sub GroupRows()

Dim BeginRow As Integer
Const Startrow As Integer = 16
Dim StepRows As Integer
Const StepDefault As Integer = 10
Dim Rowstogroup As Integer
Const GroupNrRows As Integer = 5
Dim rng As Range
Dim i As Integer

BeginRow = Application.InputBox _
("At which row to start the grouping",
Default:=Startrow, Type:=1)
If BeginRow = False Then
Exit Sub 'user hit cancel
End If

StepRows = Application.InputBox _
("HOW MANY rows are in each step?",
Default:=StepDefault, Type:=1)
If StepRows = False Then
Exit Sub 'user hit cancel
End If

Rowstogroup = Application.InputBox _
("HOW MANY rows to group?",
Default:=GroupNrRows, Type:=1)
If Rowstogroup = False Then
Exit Sub 'user hit cancel
End If

Set rng = ActiveSheet.UsedRange()
For i = BeginRow To rng(rng.Count).Row Step StepRows
Rows(i).Resize(Rowstogroup).Group
Next
ActiveSheet.Outline.ShowLevels RowLevels:=2
With ActiveSheet.Outline
.AutomaticStyles = False
.SummaryRow = xlAbove
.SummaryColumn = xlRight
End With

End Sub