VBA to group row range based on subtotal formula

[excelsishya]

Hi all ,

I am in need of code which can group data range based on subtotal formula.

a1 1
a2 3
a3 4
a4 =subtotal(9,a1:a3)
a5=7
a6=1
a7=2
a8=subtotal(9,a6:a7)
i am using excel 2007
desired output

grouping a1 :a3 and a6:a7 as they have subtotal formula

after grouping in collapse mode

a4
a5
a8

Thanks

 

[Peter_SSs]

Try this

<font face=Courier New><br><SPAN style="color:#00007F">Sub</SPAN> GroupbySubtotalFormula()<br>    <SPAN style="color:#00007F">Dim</SPAN> rFormulas <SPAN style="color:#00007F">As</SPAN> Range, c <SPAN style="color:#00007F">As</SPAN> Range<br>    <SPAN style="color:#00007F">Dim</SPAN> s <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN><br>    <br>    <SPAN style="color:#00007F">Set</SPAN> rFormulas = Range("A1", Range("A" & Rows.Count).End(xlUp)).SpecialCells(xlCellTypeFormulas)<br>    For <SPAN style="color:#00007F">Each</SPAN> c <SPAN style="color:#00007F">In</SPAN> rFormulas<br>        s = c.Formula<br>        <SPAN style="color:#00007F">If</SPAN> Left(s, 9) = "=SUBTOTAL" <SPAN style="color:#00007F">Then</SPAN><br>            s = Split(Left(s, Len(s) - 1), ",")(1)<br>            Range(s).EntireRow.Group<br>        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>    <SPAN style="color:#00007F">Next</SPAN> c<br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br></FONT>

 

[excelsishya]

Peter Thanks for the code it works perfectly well.