grouping

[mort1703]

in excel I can group rows or columns together in order to show totals only when closed and when opened it shows all the information which makes up that total, how do I write a macro to do the same?

 

[Datsmart]

To Open First level of Grouping:

    ActiveSheet.Outline.ShowLevels RowLevels:=2

To close all Group levels:

    ActiveSheet.Outline.ShowLevels RowLevels:=1

 

[brian_schneider]

Is there a way to write a macro to auto group based on changing information in a column? Similar to the subtotal function but without the sub totals? I have spreadsheets that will contain the same format of data, but there could be more of it, so writing a macro to highlight a certain rows, then selecting the group function wouldn't work.

 

[mort1703]

Rows("34:38").Select
Selection.Rows.Group

is it possible to change the numbers above to variables
ie.

startrow = 34
endrow = 38

Rows(startrow:endrow).select
selection.rows.group

 

[mort1703]

with trial and error I have worked out the code for the grouping however it didn't work as planned.

Worksheets(ASheet).Range("A1").Select
startrow = 16
ActiveCell.Offset(startrow, 0).Select
Selection.Rows.Group

this allows me to group 1 row, however I need to adjust the above to cater for multiple rows.

so I guess I need someway to select the range based on 2 variables

 

[Datsmart]

Do you want to Create Groupings or Open existing Grouped rows?

To Create a Grouping using variables you almost had the code:

Sub GroupingTest()
StartRow = 34
EndRow = 38
    Rows(StartRow & ":" & EndRow).Group
    ActiveSheet.Outline.SummaryRow = xlAbove
End Sub

Note there is no need to Select the row first, just assign the setting in the code.

I like the Outline sign Above the group instead of Below it. Remove the "xlAbove" line if you want the group sign below the grouping.
Once it is set, all Grouping for that sheet is set that way. Other worksheets will remain set to "Below" unless that sheet is also changed.