Macro to Sub total varying lists of numbers

shredda23 · Apr 17, 2014

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:

[TABLE="width: 355"]
<TBODY>[TR]
[TD][TABLE="width: 285"]
<TBODY>[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]456.00</SPAN>[/TD]
[TD="align: right"]8,798.00</SPAN>[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]45,465.00</SPAN>[/TD]
[TD="align: right"]789,789.00</SPAN>[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]45,465.00</SPAN>[/TD]
[TD="align: right"]789,789.00</SPAN>[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]45,646.00</SPAN>[/TD]
[TD="align: right"]7,897.00</SPAN>[/TD]
[/TR]
[TR]
[TD]Totals A</SPAN>[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="align: right"]45,646.00</SPAN>[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]456,465.00</SPAN>[/TD]
[TD="align: right"]45,646.00</SPAN>[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]456,465.00</SPAN>[/TD]
[TD="align: right"]1,231.00</SPAN>[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]56,456.00</SPAN>[/TD]
[TD="align: right"]45,646.00</SPAN>[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]5,465.00</SPAN>[/TD]
[TD="align: right"]56,456.00</SPAN>[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]4,546.00</SPAN>[/TD]
[TD="align: right"]45,646.00</SPAN>[/TD]
[/TR]
[TR]
[TD]Totals B</SPAN>[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]456,465.00</SPAN>[/TD]
[TD="align: right"]1,231.00</SPAN>[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]56,456.00</SPAN>[/TD]
[TD="align: right"]45,646.00</SPAN>[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]5,465.00</SPAN>[/TD]
[TD="align: right"]56,456.00</SPAN>[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]4,546.00</SPAN>[/TD]
[TD="align: right"]45,646.00</SPAN>[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]48,654.00</SPAN>[/TD]
[TD="align: right"]7,987.00</SPAN>[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]546.00</SPAN>[/TD]
[TD="align: right"]89.00</SPAN>[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]456.00</SPAN>[/TD]
[TD="align: right"]899.00</SPAN>[/TD]
[/TR]
[TR]
[TD]Totals C</SPAN>[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
</TBODY><COLGROUP><COL><COL><COL></COLGROUP>[/TABLE]
[/TD]
[TD="align: right"]</SPAN>[/TD]
[TD="align: right"]</SPAN>[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]</SPAN>[/TD]
[TD="align: right"]</SPAN>[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]</SPAN>[/TD]
[TD="align: right"]</SPAN>[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]</SPAN>[/TD]
[TD="align: right"]</SPAN>[/TD]
[/TR]
[TR]
[TD][/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="align: right"]</SPAN>[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]</SPAN>[/TD]
[TD="align: right"]</SPAN>[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]</SPAN>[/TD]
[TD="align: right"]</SPAN>[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]</SPAN>[/TD]
[TD="align: right"]</SPAN>[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]</SPAN>[/TD]
[TD="align: right"]</SPAN>[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]</SPAN>[/TD]
[TD="align: right"]</SPAN>[/TD]
[/TR]
[TR]
[TD]</SPAN>[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"][/TD]
[TD="align: right"]</SPAN>[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]</SPAN>[/TD]
[TD="align: right"]</SPAN>[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]</SPAN>[/TD]
[TD="align: right"]</SPAN>[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]</SPAN>[/TD]
[TD="align: right"]</SPAN>[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]</SPAN>[/TD]
[TD="align: right"]</SPAN>[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]</SPAN>[/TD]
[TD="align: right"]</SPAN>[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]</SPAN>[/TD]
[TD="align: right"]</SPAN>[/TD]
[/TR]
[TR]
[TD][/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
</TBODY><COLGROUP><COL><COL><COL></COLGROUP>[/TABLE]

Rick Rothstein · Apr 17, 2014

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

Code:

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 · Apr 19, 2014

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 · Apr 19, 2014

shredda23 said:
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 ..

Rich (BB 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 · Apr 21, 2014

[TABLE="class: grid, width: 500, align: center"]
<TBODY>[TR]
[TD][TABLE="width: 48"]
<COLGROUP><COL style="WIDTH: 48pt" width=64><TBODY>[TR]
[TD="class: xl114, width: 64, bgcolor: transparent"]Report:[/TD]
[/TR]
</TBODY>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][TABLE="width: 96"]
<COLGROUP><COL style="WIDTH: 48pt" span=2 width=64><TBODY>[TR]
[TD="class: xl115, width: 64, bgcolor: yellow"]MAPSDATA WITH SUBTOTALS[/TD]
[TD="class: xl114, width: 64, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl115, bgcolor: yellow"][/TD]
[TD="class: xl114, bgcolor: transparent"][/TD]
[/TR]
</TBODY>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][TABLE="width: 48"]
<COLGROUP><COL style="WIDTH: 48pt" width=64><TBODY>[TR]
[TD="class: xl114, width: 64, bgcolor: transparent"]Database:[/TD]
[/TR]
</TBODY>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Quarter Ending 3/31/2014[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][TABLE="width: 48"]
<COLGROUP><COL style="WIDTH: 48pt" width=64><TBODY>[TR]
[TD="class: xl114, width: 64, bgcolor: transparent"]Entity[/TD]
[/TR]
</TBODY>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[TD][TABLE="width: 65"]
<TBODY>[TR]
[TD]Gross Market</SPAN>[/TD]
[/TR]
</TBODY><COLGROUP><COL></COLGROUP>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][TABLE="width: 48"]
<COLGROUP><COL style="WIDTH: 48pt" width=64><TBODY>[TR]
[TD="class: xl114, width: 64, bgcolor: transparent"]Entity ID[/TD]
[/TR]
</TBODY>[/TABLE]
[/TD]
[TD][TABLE="width: 48"]
<COLGROUP><COL style="WIDTH: 48pt" width=64><TBODY>[TR]
[TD="class: xl114, width: 64, bgcolor: transparent"]Entity Name[/TD]
[/TR]
</TBODY>[/TABLE]
[/TD]
[TD][TABLE="width: 48"]
<COLGROUP><COL style="WIDTH: 48pt" width=64><TBODY>[TR]
[TD="class: xl114, width: 64, bgcolor: transparent"]Type[/TD]
[/TR]
</TBODY>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[TD][TABLE="width: 65"]
<TBODY>[TR]
[TD]value</SPAN>[/TD]
[/TR]
</TBODY><COLGROUP><COL></COLGROUP>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][TABLE="width: 48"]
<COLGROUP><COL style="WIDTH: 48pt" width=64><TBODY>[TR]
[TD="class: xl114, width: 64, bgcolor: transparent, align: right"]137702[/TD]
[/TR]
</TBODY>[/TABLE]
[/TD]
[TD][TABLE="width: 48"]
<COLGROUP><COL style="WIDTH: 48pt" width=64><TBODY>[TR]
[TD="class: xl114, width: 64, bgcolor: transparent"]gdf[/TD]
[/TR]
</TBODY>[/TABLE]
[/TD]
[TD][TABLE="width: 48"]
<COLGROUP><COL style="WIDTH: 48pt" width=64><TBODY>[TR]
[TD="class: xl114, width: 64, bgcolor: transparent"]CJV[/TD]
[/TR]
</TBODY>[/TABLE]
[/TD]
[TD][TABLE="width: 48"]
<COLGROUP><COL style="WIDTH: 48pt" width=64><TBODY>[TR]
[TD="class: xl114, width: 64, bgcolor: transparent, align: right"]2014[/TD]
[/TR]
</TBODY>[/TABLE]
[/TD]
[TD][TABLE="width: 48"]
<COLGROUP><COL style="WIDTH: 48pt" width=64><TBODY>[TR]
[TD="class: xl114, width: 64, bgcolor: transparent, align: right"]1[/TD]
[/TR]
</TBODY>[/TABLE]
[/TD]
[TD][TABLE="width: 65"]
<TBODY>[TR]
[TD="align: right"]4,564.00</SPAN>[/TD]
[/TR]
</TBODY><COLGROUP><COL></COLGROUP>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</TBODY>[/TABLE]
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 · Apr 21, 2014

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 · Apr 22, 2014

[TABLE="class: grid, width: 100, align: center"]
<TBODY>[TR]
[TD][/TD]
[TD]a[/TD]
[TD]b[/TD]
[TD]c[/TD]
[TD]d[/TD]
[TD]e[/TD]
[TD]f[/TD]
[TD]g[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Report:[/TD]
[TD]Title[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Database:[/TD]
[TD]title[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]data[/TD]
[TD]data[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]Entity ID[/TD]
[TD]Entity name[/TD]
[TD]Type[/TD]
[TD]year[/TD]
[TD]Qtr[/TD]
[TD]GMV[/TD]
[TD]Encumbrance[/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]546546[/TD]
[TD]name[/TD]
[TD]CJV[/TD]
[TD]2014[/TD]
[TD]1[/TD]
[TD]4564564[/TD]
[TD]4654654[/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD]546545[/TD]
[TD]name[/TD]
[TD]cjv[/TD]
[TD]2014[/TD]
[TD]1[/TD]
[TD]47879[/TD]
[TD]4687987[/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD]75465[/TD]
[TD]name[/TD]
[TD]cjv[/TD]
[TD]2014[/TD]
[TD]1[/TD]
[TD]46556[/TD]
[TD]465456[/TD]
[/TR]
[TR]
[TD]13[/TD]
[TD][/TD]
[TD]CJV subtotal[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]?[/TD]
[TD]?[/TD]
[/TR]
[TR]
[TD]14[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]15[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]16[/TD]
[TD]465466[/TD]
[TD]Name[/TD]
[TD]EJV[/TD]
[TD]2014[/TD]
[TD]1[/TD]
[TD]546546[/TD]
[TD]465456[/TD]
[/TR]
[TR]
[TD]17[/TD]
[TD]454654[/TD]
[TD]name[/TD]
[TD]Ejv[/TD]
[TD]2014[/TD]
[TD]1[/TD]
[TD]87987[/TD]
[TD]7897987[/TD]
[/TR]
[TR]
[TD]18[/TD]
[TD]446454[/TD]
[TD]name[/TD]
[TD]EJv[/TD]
[TD]2014[/TD]
[TD]1[/TD]
[TD]4456[/TD]
[TD]87897[/TD]
[/TR]
[TR]
[TD]19[/TD]
[TD][/TD]
[TD]EJV subtotal[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]?[/TD]
[TD]?[/TD]
[/TR]
</TBODY>[/TABLE]

shredda23 · Apr 22, 2014

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 · Apr 22, 2014

shredda23 said:
Let me know If this helps.

Yes that helps, but what about this?

Peter_SSs said:
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 · Apr 22, 2014

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.

Macro to Sub total varying lists of numbers

shredda23

New Member

Excel Facts

Rick Rothstein

MrExcel MVP

shredda23

New Member

Peter_SSs

MrExcel MVP, Moderator

shredda23

New Member

Peter_SSs

MrExcel MVP, Moderator

shredda23

New Member

shredda23

New Member

Peter_SSs

MrExcel MVP, Moderator

shredda23

New Member

Similar threads

Forum statistics

Share this page

Macro to Sub total varying lists of numbers

New Member

Excel Facts

MrExcel MVP

New Member

MrExcel MVP, Moderator

New Member

MrExcel MVP, Moderator

New Member

New Member

MrExcel MVP, Moderator

New Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock