PDA

View Full Version : Solved: Group rows



peer15
10-23-2007, 03:59 AM
I want to group a dynamic set of rows and a summarization of all values for that area by using a macro.

Thx,
Peer

Bob Phillips
10-23-2007, 04:12 AM
.

peer15
10-23-2007, 06:50 AM
Sorry for being a newbee but I guess I wasn't clear about my problem.

I need a macro that automatically groups all rows within an area and calculates the totals per group (per column).
Normal output is cel A1 until E17 but the number of rows or columns can be more or less (per area). So I think I need VBA fro grouping especially when the number of rows increases within one area.

Thnx,
Peer.

Bob Phillips
10-23-2007, 07:18 AM
Insert a row and see what happens.

peer15
10-23-2007, 08:27 AM
That works fine, nice job.
However I copy the data from an external source into an empty sheet. After that I want to run a macro that automatically groups the areas using the grouping functionality. Only if I want to see the underlying data I want to ungroup the area by clicking "+ ".
What you see at the bottom is what I want as a result. I have added that manually but I don't want to group all rows manually all the time. Please forget row 24 until 40.

Nurofen
10-23-2007, 09:05 AM
Sub gRounp()
Range("A3:A6").Rows.Group
Range("A9:A10").Rows.Group

' and so on
End Sub

Bob Phillips
10-23-2007, 09:45 AM
Public Sub ProcessData()
Dim i As Long
Dim iLastRow As Long
Dim iStart As Long
Dim iLastOff As Long

With ActiveSheet

iLastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
iLastOff = 4
.Rows(1).Copy .Rows(iLastRow + iLastOff - 1)
For i = 1 To iLastRow
If .Cells(i, "A").Value <> "" Then
.Cells(iLastRow + iLastOff, "A").Value = .Cells(i, "A").Value
iStart = i
Do
i = i + 1
Loop Until .Cells(i, "A").Value = ""
.Cells(iLastRow + iLastOff, "C").Resize(, 3).FormulaR1C1 = _
"=SUM(R" & iStart & "C:R" & i & "C)"
iLastOff = iLastOff + 2
End If
Next i
End With

End Sub

peer15
10-23-2007, 11:16 PM
Nurofen's solution is what I'm looking for only it isn't dynamic. If I have more rows within one area, I have to redefine the VBA. On the other hand, Xld's solution is very dynamic but it doesn't provide me any grouping. I guess I have to find a way to combine both solutions.

Thank you both!

Bob Phillips
10-24-2007, 12:31 AM
What doi you mean by it doesn't provide any grouping? It provides a table of results exactly as you showed in your example

peer15
10-24-2007, 03:53 AM
I mean that it doesn't give me a choice to group or ungroup the rows. I want to see a high level (grouped and summarized per area) overview when I open the file and if I need more detail information for an area I can ungroup by clicking the "+" sign (per area). In that case the original data becomes visible.

peer15
10-24-2007, 02:01 PM
... I have updated the file. On sheet1 you will find the raw data that I have imported in Excel. I have added an extra column to the raw data for the Area.
In sheet2 you will find what I want to accomplish. The red text should be added and I want to group all rows within an Area. An Area can contain more or less countries everytime I run an export to Excel.

Could anyone please take a look at this because so far i wasn't able to solve it myself.

Many thanks!

Bob Phillips
10-24-2007, 03:51 PM
Public Sub ProcessData()
Dim i As Long
Dim iLastRow As Long
Dim iStart As Long

With ActiveSheet

iLastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
For i = 1 To iLastRow
If .Cells(i, "A").Value <> "" Then
iStart = i
Do
i = i + 1
Loop Until .Cells(i, "A").Value = ""
.Cells(iStart, "D").Resize(, 3).FormulaR1C1 = _
"=SUM(R" & iStart + 1 & "C:R" & i & "C)"
.Rows(iStart + 1).Resize(i - iStart - 2).Group
End If
Next i
.Outline.ShowLevels RowLevels:=1
End With

End Sub

peer15
10-24-2007, 11:11 PM
Thanks for your effort! This is what I needed.

Regards,
Peer.