Macro to Sub total varying lists of numbers

S

shredda23

New Member
Joined
Apr 16, 2014
Messages
44
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]
 
Sort by date Sort by votes
shredda23 said:
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.
Click to expand...
We may need to be more specific in searching for the "subtotal" cells in column B, but give this a try in a copy of your workbook.
Rich (BB code): 
Sub DoSubTotals()
  Dim oSet As Long
  Dim rng As Range
  
  Const TotalLabelsCol As String = "B"  '<- Change if required
  Const FirstDataCol As String = "F"    '<- Change if required
  Const FirstDataRow As Long = 10       '<- Change if required
  Const NumFormulaCols As Long = 20     '<- Change if required
  Const fBase As String = "=SUM(R#C:R[-1]C)"
  
  oSet = Columns(FirstDataCol).Column - Columns(TotalLabelsCol).Column
  For Each rng In Range(TotalLabelsCol & FirstDataRow, _
      Range(TotalLabelsCol & Rows.Count).End(xlUp)).SpecialCells(xlConstants).Areas
  rng.Select
    rng.Cells(rng.Rows.Count, 1).Offset(, oSet).Resize(1, NumFormulaCols).FormulaR1C1 _
      = Replace(fBase, "#", rng.Row, 1, 1, 1)
  Next rng
End Sub
 
Upvote 0

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.
shredda23 said:
wow thanks a million and sorry for all the confusion. This works perfectly!!

Thanks!!!
Click to expand...
Great! :)

I just realised that I forgot the "rng.Select" line in my code. I had that in for myself while testing but it is not required, will slow the code and will probably make the screen flicker needlessly while the code runs. Best to remove it.
 
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Solved
how to macro to auto sub-total and grand total of manpower plan used in the project
Replies
1
Views
248
david2005wang
D
B
  • Question Question
Macro to create a new row and copy data
Replies
1
Views
640
DanteAmor
DanteAmor
R
  • Question Question
Line Manager Formula/Macro
Replies
6
Views
984
RasGhul
RasGhul
V
  • Question Question
VBA urgent help needed
Replies
5
Views
919
Vbalearner85
V
D
  • Question Question
VBA to consolidate and sum values
Replies
5
Views
5K
DDePoy
D

Forum statistics

Threads
1,223,244
Messages
6,170,976
Members
452,372
Latest member
Natalie18

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top