Grouping + Series

[Jbelle7435]

You know when you drag the Black Plus Sign down you make a series to your liking!

Well I have groups. For example two and they are one level each. Is it possible to have the actual groups get more indepth when you drag the plus sign(maybe series..) to get those 2.1,2.2,etc. What you see after the commas is how it comes out 1 thru 11. I want the one to the left of the Comma. Will the computer know the open group(-) exist when doing the series drag??? Otherwise I manually drag series up to the top of the group and then do it one more time with the .1 step value.Becomes very tedious when the list gets big and needs to be redone/edited!

1,1
-2,2
2.1,3
2.2,4
2.3,5
3,6
4,7
-5,8
5.1,9
5.2,10
5.3,11
etc..
..
.

[arlu1201]

Do you have a sample file that you can upload? I think some formulae might work this.

[Jbelle7435]

Do you have a sample file that you can upload? I think some formulae might work this.

See attachment

I did some macro research:
1. When you select the plus/minus signs to open and close a group this shows up.

Sub Macro3()
'
' Macro3 Macro
'

'
End Sub

When you open and close all of the groups via 1,2 on the top this shows...

Sub Macro4()
'
' Macro4 Macro
'

'
    ActiveSheet.Outline.ShowLevels RowLevels:=1
    ActiveSheet.Outline.ShowLevels RowLevels:=2
    ActiveSheet.Outline.ShowLevels RowLevels:=1
End Sub

I wonder...

[Jbelle7435]

I created a new workbook. If you erase all the cell values and close the groups(+), then play the Macro it will open the groups and fill it in but still had to Record the Macro and do everything manually!!

Sub Macro9()
'
' Macro9 Macro
'

'
    ActiveSheet.Outline.ShowLevels RowLevels:=2
    Range("A2").Select
    ActiveCell.FormulaR1C1 = "1"
    Range("A3").Select
    ActiveCell.FormulaR1C1 = "1.1"
    Range("A4").Select
    ActiveCell.FormulaR1C1 = "1.2"
    Range("A5").Select
    ActiveCell.FormulaR1C1 = "1.3"
    Range("A6").Select
    ActiveCell.FormulaR1C1 = "1.4"
    Range("A7").Select
    ActiveCell.FormulaR1C1 = "2"
    Range("A8").Select
    ActiveCell.FormulaR1C1 = "2.1"
    Range("A9").Select
    ActiveCell.FormulaR1C1 = "2.2"
    Range("A10").Select
    ActiveCell.FormulaR1C1 = "2.3"
    Range("A11").Select
    ActiveCell.FormulaR1C1 = "2.4"
    Range("A12").Select
    ActiveCell.FormulaR1C1 = "3"
    Range("A13").Select
    ActiveCell.FormulaR1C1 = "4"
    Range("A14").Select
End Sub

[arlu1201]

Using the recorded version will not work for you.

You will have other data in columns B onwards right? So the file cannot be cleared each time you want to group the sections?

[Jbelle7435]

Yeah, we both know the recorded version will not work as I would have to make a NEW version everytime. This is basically what I think I want to happen and a Macro can do that but needs to be smart enough to figure the rows that are grouped and that are not grouped. If it can't then, I don't think it can be done.

1. OPEN UP ALL THE GROUPS SO ALL THE ROWS ARE KNOWN(GROUPED OR NOT)
2. FILL IN THE # COLUMN WITH THE APPROPROATE VALUES BUT KNOW THE DIFFERENCE WHEN IN A GROUP AND NOT!

Thats basically it. Each time I need to do this post modification of the rows(removed/added) from 1 thru .......

Its like breaking down the Series Drag into two but the computer needs to realize...

[arlu1201]

Will each number always have 4 sub-numbers? 1.1 to 1.4?

[Jbelle7435]

Will each number always have 4 sub-numbers? 1.1 to 1.4?

Unfortuantely no. Some grouped cells might have more rows than others! but want to see what direction your going? Macro, or formula, or other

Anyone can Fill!. Anyone can make OUTLINES(Groups) But how do you mix them together!

[arlu1201]

So the data will be grouped previously without the numbers?

[Jbelle7435]

So the data will be grouped previously without the numbers?

I don't understand this statement?

If its not in a group/outline I want whole numbers. If its in a group I want that 1st value on the top of the group X with the X.1,X.2,X.3,X.4,X.5,etc...... for each row below it! I can't explain it any better. ALL IN ORDER from 1

http://database.ittoolbox.com/groups...d-data-3364263 This link might lead me in the right direction...

[arlu1201]

I was thinking of a macro solution. But for that i need to know what the "before macro" file looks like. So i can then work towards getting the "after macro" file.

[Jbelle7435]

Basically it will be always selecting an entire column that could change. So new selection will be made. But whatever is selected should produce and know the difference between the hidden cells. Untitleda.png<---This is half the battle. It worked but now my group cells are blank and the valueof the unhidden cells stay the same(ctrl+Enter) for the 1st Row. I am going in the right direction hopefully.

Is there a shortcut to AutoFill a selected row of cells?

[Jbelle7435]

Sub Macro10()
'
' Macro10 Macro
'

'
    Range("A2:A100").Select
    Selection.SpecialCells(xlCellTypeVisible).Select
    Selection.FormulaR1C1 = "3"
End Sub

For this example above...
I don't mind selecting the cells and changing line:Range("A2:A100").Select if I have to in my code but now I have to figure out how to get the

1. series command to work on those unhidden cells because right now the Ctrl+Enter copies the 1st cell
2. Fill in those hidden cells somehow with the .1,.2,.3

then we might have a winner.

[Jbelle7435]

Sub Macro18()
'
' Macro18 Macro
'

'
    Range("A2:A10").Select   '<----the "A2:A#" # VALUE NEEDS TO BE UPDATED as per use of this macro
    Selection.SpecialCells(xlCellTypeVisible).Select
    Selection.DataSeries Rowcol:=xlColumns, Type:=xlLinear, Date:=xlDay, _
        Step:=1, Trend:=False
End Sub

(1)In my macro you need to leave the 1st value always in the 1st row being selected. My case being 1.
(2)Still need to fill in the hidden cells with the X.1,X.2,X.3,etc the its finished! <---this is the tricky part. I am done with the Unhidden Cells and got it how I want it.

I am not a macromaster but need a push to get me (2)

[Jbelle7435]

Sub Macro20()
'
' Macro20 Macro
'

'
    Application.Goto Reference:="BBALKLK"
    Selection.DataSeries Rowcol:=xlColumns, Type:=xlLinear, Date:=xlDay, _
        Step:=0.1, Trend:=False
    Application.Goto Reference:="DFGHJK"
    Selection.DataSeries Rowcol:=xlColumns, Type:=xlLinear, Date:=xlDay, _
        Step:=0.1, Trend:=False
End Sub

Ok.. Naming Ranges Helped. So I will have to Name all each top cell along with the hidden(group) cells each range. Work up front might be less work later for the MACRO to know what going on...

Funny names(whatever..)

What I did makes sense but the day I have to remove/add rows I wonder how these ranges will be affected. I.E having to change and keep track maybe a bit too much....

[Jbelle7435]

hmmm. Can you fill series of separate ranges in one OK??

Separate Columns work
1 ---------------9
1.1 ----------- 9.1
1.2 ------------9.2

------------------but not in the same Column(see below).Unless someone else knows...

1
1.1
1.2

9(1.3)
9.1(1.4)
9.2(1.5)

Whats in () is what I am getting


hmmm. If I name each range one then that might help...

[Jbelle7435]

Sub Macro18()
'
' Macro18 Macro
'

'
    Range("A2:A20").Select '<----the "A2:A#" # VALUE NEEDS TO BE UPDATED as per use of this macro
    Selection.SpecialCells(xlCellTypeVisible).Select
    Selection.DataSeries Rowcol:=xlColumns, Type:=xlLinear, Date:=xlDay, _
        Step:=1, Trend:=False
        
            ActiveSheet.Outline.ShowLevels RowLevels:=2
            
            
            
            Application.Goto Reference:="RANGE1"
    Selection.DataSeries Rowcol:=xlColumns, Type:=xlLinear, Date:=xlDay, _
        Step:=0.1, Trend:=False
    Application.Goto Reference:="RANGE2"
    Selection.DataSeries Rowcol:=xlColumns, Type:=xlLinear, Date:=xlDay, _
        Step:=0.1, Trend:=False
            Application.Goto Reference:="RANGE3"
    Selection.DataSeries Rowcol:=xlColumns, Type:=xlLinear, Date:=xlDay, _
        Step:=0.1, Trend:=False
        
        
End Sub

Right now it can't be any better. The only editing I have to do from here is make more: Range4,5,6,etc.... and copy whats in bold and update the selection Line at the top unless someone can shorten that code

------------------------code formatting issue only below--------------

RANGE3,RANGE4,RANGE5 WON'T WORK. It only works off the 1st range, RANGE 3. Hmmmmm

[arlu1201]

I have actually asked some of my friends here to assist you. I am totally lost, sorry

[Jbelle7435]

I have actually asked some of my friends here to assist you. I am totally lost, sorry

Whats the concern.
What I want when the group is closed
1
2
+3
4
+5
6
7


What I want when the group is open
1
2
-3
3.1
3.2
3.3
4
5
5.1
5.2
6
7

I basically figured it out with my macro but I just need to format my code better. I made multiple ranges and repeat the same three lines over and over. Wanted to know if it can be consolidated

I have 2003 at my house and the code will not fill a series for visible cells only! This code is good for the 2007, later versions