Macro to Sub total varying lists of numbers

[shredda23]

Hi I am fairly new to excel and I am having trouble trying to get a sub total function to work for me. I need the subtotal to for a list of about 20 columns in one line with a varying amount of rows. So the subtotal must stop when there is a blank at the top. I can get the macro to find the totals A on the spreadsheet and then offset to beneath the columns of numbers but the sub total formulas takes in every number. I'm hoping somone can help. Here is the example:

456.00</SPAN> 8,798.00</SPAN> 45,465.00</SPAN> 789,789.00</SPAN> 45,465.00</SPAN> 789,789.00</SPAN> 45,646.00</SPAN> 7,897.00</SPAN> Totals A</SPAN> 45,646.00</SPAN> 456,465.00</SPAN> 45,646.00</SPAN> 456,465.00</SPAN> 1,231.00</SPAN> 56,456.00</SPAN> 45,646.00</SPAN> 5,465.00</SPAN> 56,456.00</SPAN> 4,546.00</SPAN> 45,646.00</SPAN> Totals B</SPAN> 456,465.00</SPAN> 1,231.00</SPAN> 56,456.00</SPAN> 45,646.00</SPAN> 5,465.00</SPAN> 56,456.00</SPAN> 4,546.00</SPAN> 45,646.00</SPAN> 48,654.00</SPAN> 7,987.00</SPAN> 546.00</SPAN> 89.00</SPAN> 456.00</SPAN> 899.00</SPAN> Totals C</SPAN> <TBODY> </TBODY><COLGROUP><COL><COL><COL></COLGROUP>	</SPAN>	</SPAN>
	</SPAN>	</SPAN>
	</SPAN>	</SPAN>
	</SPAN>	</SPAN>
		</SPAN>
	</SPAN>	</SPAN>
	</SPAN>	</SPAN>
	</SPAN>	</SPAN>
	</SPAN>	</SPAN>
	</SPAN>	</SPAN>
</SPAN>
		</SPAN>
	</SPAN>	</SPAN>
	</SPAN>	</SPAN>
	</SPAN>	</SPAN>
	</SPAN>	</SPAN>
	</SPAN>	</SPAN>
	</SPAN>	</SPAN>

<TBODY>
</TBODY><COLGROUP><COL><COL><COL></COLGROUP>

 

[Rick Rothstein]

If I understand what you want and your data layout correctly, then I think this macro will do what you are asking for...

Sub DoTotals()
  Dim C As Long, A As Long, ColumnRange As Range, TotalRows As Range, TotalCells As Range, Ar As Range
  Set ColumnRange = Range("B1", Cells.Find("*", , xlValues, , xlByColumns, xlPrevious)).EntireColumn
  Set TotalRows = Union(Rows(1), Columns("A").SpecialCells(xlConstants).EntireRow)
  Set TotalCells = Intersect(TotalRows, ColumnRange)
  For A = 2 To TotalCells.Areas.Count
    For C = 2 To ColumnRange.Columns.Count - 1
      With TotalCells
        Cells(.Areas(A).Row, C).Value = WorksheetFunction.Sum(Range(Cells(.Areas(A - 1). _
                                        Offset(-(.Areas(A - 1).Row <> 1)).Row, C), _
                                        Cells(.Areas(A).Offset(-1).Row, C)))
      End With
    Next
  Next
End Sub

 

[shredda23]

Wow thanks for the help. I was just wondering what part of the code I need to alter say If my data started in column D or E and also the number of columns I needed subtotals was say 20 to 25. Also is it possible to have the sub total formula to show up in the totals cells. Would just need for audit purposes.

Thanks for the help!!!

 

[Peter_SSs]

Wow thanks for the help. I was just wondering what part of the code I need to alter say If my data started in column D or E and also the number of columns I needed subtotals was say 20 to 25. Also is it possible to have the sub total formula to show up in the totals cells. Would just need for audit purposes.

Thanks for the help!!!

I've assumed ..
- that the final column can be determined from headings (or data) in row 1
- that you can change the code to reflect the first column of data to be summed and the column that the 'Totals' labels are in
- that otherwise the layout is as shown here.

Excel Workbook
	B	C	D	E	F	G
1			H1	H2	H3	H4
2			57.00	70.00	92.00	84.00
3			3.00	55.00	92.00	44.00
4			68.00	51.00	52.00	47.00
5			36.00	41.00	27.00	6.00
6	Totals A
7			37.00	49.00	16.00	48.00
8			26.00	63.00	55.00	16.00
9			94.00	66.00	51.00	40.00
10			11.00	79.00	46.00	76.00
11			60.00	84.00	2.00	22.00
12			8.00	11.00	34.00	13.00
13	Totals B
14			83.00	9.00	20.00	68.00
15			46.00	36.00	15.00	71.00
16			93.00	54.00	9.00	76.00
17			41.00	47.00	50.00	21.00
18			33.00	10.00	59.00	17.00
19			93.00	10.00	45.00	28.00
20			88.00	76.00	28.00	68.00
21			26.00	9.00	4.00	33.00
22	Totals C
23
SubTotals

The code ..

Sub DoSubTotals()
  Dim lLastcol As Long, lFDC As Long, lTLC As Long
  Dim rBlank As Range
  
  Const TotalLabelsCol As String = "B"  '<- Change to suit
  Const FirstDataCol As String = "D"    '<- Change to suit
  Const fBase As String = "=SUM(R#C:R[-1]C)"
  
  lLastcol = Cells(1, Columns.Count).End(xlToLeft).Column
  lTLC = Columns(TotalLabelsCol).Column
  lFDC = Columns(FirstDataCol).Column
  For Each rBlank In Columns(lTLC).SpecialCells(xlBlanks).Areas
    rBlank.Offset(rBlank.Rows.Count, lFDC - lTLC).Resize(1, lLastcol - lFDC + 1).FormulaR1C1 _
      = Replace(fBase, "#", rBlank.Row, 1, 1, 1)
  Next rBlank
End Sub

The result (with just one formula per row shown) ..

Excel Workbook
	B	C	D	E	F	G
1			H1	H2	H3	H4
2			57.00	70.00	92.00	84.00
3			3.00	55.00	92.00	44.00
4			68.00	51.00	52.00	47.00
5			36.00	41.00	27.00	6.00
6	Totals A		164.00	217.00	263.00	181.00
7			37.00	49.00	16.00	48.00
8			26.00	63.00	55.00	16.00
9			94.00	66.00	51.00	40.00
10			11.00	79.00	46.00	76.00
11			60.00	84.00	2.00	22.00
12			8.00	11.00	34.00	13.00
13	Totals B		236.00	352.00	204.00	215.00
14			83.00	9.00	20.00	68.00
15			46.00	36.00	15.00	71.00
16			93.00	54.00	9.00	76.00
17			41.00	47.00	50.00	21.00
18			33.00	10.00	59.00	17.00
19			93.00	10.00	45.00	28.00
20			88.00	76.00	28.00	68.00
21			26.00	9.00	4.00	33.00
22	Totals C		503.00	251.00	230.00	382.00
23
SubTotals

 

[shredda23]

Report: <COLGROUP><COL style="WIDTH: 48pt" width=64><TBODY> </TBODY>					MAPSDATA WITH SUBTOTALS <COLGROUP><COL style="WIDTH: 48pt" span=2 width=64><TBODY> </TBODY>
Database: <COLGROUP><COL style="WIDTH: 48pt" width=64><TBODY> </TBODY>					Quarter Ending 3/31/2014
		Entity <COLGROUP><COL style="WIDTH: 48pt" width=64><TBODY> </TBODY>			Gross Market</SPAN> <TBODY> </TBODY><COLGROUP><COL></COLGROUP>
Entity ID <COLGROUP><COL style="WIDTH: 48pt" width=64><TBODY> </TBODY>	Entity Name <COLGROUP><COL style="WIDTH: 48pt" width=64><TBODY> </TBODY>	Type <COLGROUP><COL style="WIDTH: 48pt" width=64><TBODY> </TBODY>			value</SPAN> <TBODY> </TBODY><COLGROUP><COL></COLGROUP>
137702 <COLGROUP><COL style="WIDTH: 48pt" width=64><TBODY> </TBODY>	gdf <COLGROUP><COL style="WIDTH: 48pt" width=64><TBODY> </TBODY>	CJV <COLGROUP><COL style="WIDTH: 48pt" width=64><TBODY> </TBODY>	2014 <COLGROUP><COL style="WIDTH: 48pt" width=64><TBODY> </TBODY>	1 <COLGROUP><COL style="WIDTH: 48pt" width=64><TBODY> </TBODY>	4,564.00</SPAN> <TBODY> </TBODY><COLGROUP><COL></COLGROUP>

<TBODY>
</TBODY>

Hi I think we are almost there. The formula is not working correctly in my workbook but I'm guessing it is because of the way my data is laid out. My total heading is in column b and the totals should start formulating in col f accross to col y. There are headings and report titles in rows 1-10 of the worksheet. When I run the macro you sent me it creates a formula in cell F9(where value is typed in the table above) totaling from F3 to f 8, then again in F106 doing a sub total of f104 to f105 right at the beginning of where my next group of numbers begins and repeats the same thing. It does not do any subtotals in any other column other than F. Is it possible for me to attach my worksheet as It may be easier for you to see and work with? Ive tried to put a sample of the way the beginning of the report should look like above.

 

[Peter_SSs]

It seems to me like your layout is not nearly as simple as your original sample, and that the assumptions I listed are not correct, so it isn't surprising that it is not working as hoped.

You cannot attach an actual file, nor should we need one with hundreds of rows. You can post small screen shots, as I did, using one of the methods suggested in my signature block below.
Ideally you can make up some dummy data that reflects the gist and any variances in your layout but with just say 3 sections, the data to sum in, say columns F:H only and no more than about 4 rows to actually add in each section and post that as suggested. Advantage is that we can not only see row/column labels and actual layout, but can copy the data to a worksheet to test.

It would also be good to know how to determine the last column to sum. My assumption was that it was the last column with something in row 1 but that apparently wasn't the case. Perhaps for you it is another particular row, or do we need to find the last column that has data anywhere in it?

 

[shredda23]

	a	b	c	d	e	f	g
1	Report:	Title
2	Database:	title
3
4
5
6
7
8						data	data
9	Entity ID	Entity name	Type	year	Qtr	GMV	Encumbrance
10	546546	name	CJV	2014	1	4564564	4654654
11	546545	name	cjv	2014	1	47879	4687987
12	75465	name	cjv	2014	1	46556	465456
13		CJV subtotal				?	?
14
15
16	465466	Name	EJV	2014	1	546546	465456
17	454654	name	Ejv	2014	1	87987	7897987
18	446454	name	EJv	2014	1	4456	87897
19		EJV subtotal				?	?

<TBODY>
</TBODY>

 

[shredda23]

Above is a basic sample of the data. The main differences being that the subtotals needed would go across 20 columns and the number of CJVs would be approx 200, eJVs approx. 120. There will also be around 10 different categories of entity type. Let me know If this helps.

 

[Peter_SSs]

Let me know If this helps.

Yes that helps, but what about this?

It would also be good to know how to determine the last column to sum. My assumption was that it was the last column with something in row 1 but that apparently wasn't the case. Perhaps for you it is another particular row, or do we need to find the last column that has data anywhere in it?

Can I safely look across row 9 to find what the last column actually is?
Or is the number of columns always exactly 20 (starting from column F)?

Also ..

Is row 10 always the first row of actual data to be included in the calculations?
If not, how would I determine what row contained the first data to be added?

 

[shredda23]

The data will always start in row 10 and the number of columns will always be from col f to clomun Y so 20 columns.