Solved: Finding and manipulating cell ranges

[mmx2]

To start off with I know very little VBA and I'm not finding many sources of info that will teach me how to do what I'm wanting to do(or maybe I'm not understanding what I'm reading) So I'm hoping someone here can help me. It sounded simple in my mind. lol

I have a spread sheet with thousands of rows. All of the data in the A column (always starting with Cell A7) it has grouped numbers 100, 200, 700 for each row. The numbers vary but are always grouped and the amount of rows for each group varies as well.

I'm trying to figure out how you can find the cell range of all 100's. Then sum them up and place it in the last row of 100's F column.

In this case it would be for the first group range would be A7:A16 and the added data (which should be 1,000) would be placed into F16. And then move onto the next range of numbers which would be 200 repeat the above and then move onto 700 and repeat until the end of the sheet. The rest of the data in the sheet is just for display purposes and shouldn't matter and is varied in actual naming. Everything varies from month to month so the amount of rows, code numbers, and placement of the totals is never in the same row but is always in A and F

I repeat I don't really know what I'm doing here but I know this can be done it seems so simple and I'm at loss.

[BrianMH]

You don't need VBA you just need to use grouping. If your using 2007 go to the data tab. Highlight your data. Click group and then click subtotal.

[Simon Lloyd]

this has had a minimal test but should do what you want (there are smarter ways of doing this )[VBA]Sub Total_Formula()
Dim Rng As Range, i As Long, Cnt As Long
i = Range("A" & Rows.Count).End(xlUp).Row
Do
Set Rng = Range("A7:A" & Range("A" & Rows.Count).End(xlUp).Row)
Cnt = Application.WorksheetFunction.CountIf(Rng, Range("A" & i).Value)
Range("E" & i).Value = Range("A" & i).Value & " Total:"
Range("F" & i).Formula = "=sum(A" & i + 1 - Cnt & ":A" & i & ")"
i = i - Cnt
Loop Until i = 6
End Sub[/VBA]

[mmx2]

this has had a minimal test but should do what you want (there are smarter ways of doing this )

Thank You! Your a life saver!

You don't need VBA you just need to use grouping. If your using 2007 go to the data tab. Highlight your data. Click group and then click subtotal.

Wish I could sadly our office computers likely won't see 2007 until 2015

seriously

[BrianMH]

http://www.ehow.com/how_4499838_grou...oft-excel.html
Can do it in office 2003 too. Not sure if its available in 97. But if the macro works thats all good too.

[Simon Lloyd]

If this is solved please go to your first post and above it to the right you will see "Thread Tools" in this dropdown there is an option to mark the thread solved