Need help with dynamic subtotal & row count

psulion01

Board Regular
Joined
Sep 25, 2002
Messages
127
The tablw below shows what I'm working with. The number of data rows will be dynamic from day to day, but the overall structure of the sheet should look the same.

How do I code:

1) The subtotals so they work whether I have 1 row of data or hundreds? The output will always be two rows below the last line of data.

2) How do I count the number of rows of data and display under the subtotal (i.e. on this sheet there were 5 rows). This will count all rows, not just those with unique data.

Thx in advance...you guys are GOOOOOD at this stuff! ****!
Daily Trade Summary Macro ver 3.1.xls
ABCDEFGHIJ
19/28/2002
2
3A/CNAMEA/C#B/SSYMSHSPRICEDATEORDER#PCsLostPCs
4SMITHSELLABC6,0009/27/20021234$300.00$0.00
5SMITHSELLDEF12,0009/27/20021235$600.00$0.00
6SMITHSELLHIJ5009/27/20021236$0.00$25.00
7SMITHSELLKLM2009/27/20021237$0.00$10.00
8SMITHSELLNOP10,5009/27/20021238$525.00$0.00
9
1029,200$1,425.00$35.00
11555
data (16)
 

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.

Dave Patton

Well-known Member
Joined
Feb 15, 2002
Messages
4,970
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
You could consider built in functions.

If you require information by particular names etc consider Data Filter.

If you put your totals and counts above the
Data, The Function SubTotal can provide Sum, Max, Count etc of the Data. Subtotal works on visible data.

see Help in Excel for the various parameters.
This message was edited by Dave Patton on 2002-09-29 15:25
 

psulion01

Board Regular
Joined
Sep 25, 2002
Messages
127
Thanks Dave... SUBTOTAL should work but I need help coding it. Here's what I have (I don't know how to define the last row to be included in the subtotal. the last row will change every time I run the macro, so how do I code it to work dynamically?)

Here's what I have so far (for Column E only):


' Sum the columns

Dim all As Range
Set all = Range("E65536").End(xlUp)


Range("E65536").End(xlUp).Offset(2, 0).Select
ActiveCell.FormulaR1C1 = "=SUBTOTAL(9,r4c5:all)"
Range("E65536").End(xlUp).Offset(1, 0).Select
Selection.NumberFormat = "General"
ActiveCell.FormulaR1C1 = "=SUBTOTAL(2,r4c5:all)"
 

Dave Patton

Well-known Member
Joined
Feb 15, 2002
Messages
4,970
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
On 2002-09-29 08:29, psulion01 wrote:
Thanks Dave... SUBTOTAL should work but I need help coding it. Here's what I have (I don't know how to define the last row to be included in the subtotal. the last row will change every time I run the macro, so how do I code it to work dynamically?)

Here's what I have so far (for Column E only):


' Sum the columns

Dim all As Range
Set all = Range("E65536").End(xlUp)


Range("E65536").End(xlUp).Offset(2, 0).Select
ActiveCell.FormulaR1C1 = "=SUBTOTAL(9,r4c5:all)"
Range("E65536").End(xlUp).Offset(1, 0).Select
Selection.NumberFormat = "General"
ActiveCell.FormulaR1C1 = "=SUBTOTAL(2,r4c5:all)"

I defer to others re VBA.

You could work out a solution using standard functions and then decide what you want to do with VBA.

1. Can you put your totals and counts at the top instead of at the bottom?

2. Can you create a Dynamic named Table
( search this forum for help if required).

3. Create Data filter and then review results.

4. Review various option parameters with Subtotal (see Help).
This message was edited by Dave Patton on 2002-09-29 10:48
 

psulion01

Board Regular
Joined
Sep 25, 2002
Messages
127

ADVERTISEMENT

-bump-

Anyone handy with VBA care to offer some help?
 

Dave Patton

Well-known Member
Joined
Feb 15, 2002
Messages
4,970
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
On 2002-09-29 14:43, psulion01 wrote:
-bump-

Anyone handy with VBA care to offer some help?

You did not advise
- if totals could go above data
- if you are using Autofilter
- why you require VBA

If you set up your database with Dynamic Range(s).

To use VBA to run a particular filter

Sub Macro1()

Selection.AutoFilter Field:=1, Criteria1:="Smith"
End Sub
 

psulion01

Board Regular
Joined
Sep 25, 2002
Messages
127
Dave,

I'm not sure what you're talking about with the autofilters.

No, I can't have the subtotals at the top. I need it just the way my example shows. The # of rows will be changing every time I create the sheet, so the macro must be able to dynamically place the subtotal 2 rows below the last row of data. One row below the subtotal I want a count of how many data rows were used to create the subtotal.

Is it clear now?

-- Better yet, is there a way to call a definied range in a formula? i.e. subtotal(2,range1) where range1 = a dynamic range.
This message was edited by psulion01 on 2002-09-29 19:55
 

Tom Urtis

MrExcel MVP
Joined
Feb 10, 2002
Messages
11,257
Here's a macro that might do what you want.

It is redundant on purpose (the same two functions on 3 separate columns) for 2 reasons:

(1) It gives you flexibility to modify the column references in case you add or delete columns.

(2) There is different formatting (currency and non-currency) in your data so this code reflects that in the calculated Sum value display.

Here's the macro:

Sub KalckIt()

'Column E, SHS
With Range("E4").End(xlDown).Offset(2, 0)
.Value = WorksheetFunction.Sum(Range("E4", Range("E4").End(xlDown)))
.NumberFormat = "#,##0"
.Font.Bold = True
End With
With Range("E4").End(xlDown).Offset(3, 0)
.Value = WorksheetFunction.CountA(Range("E4", Range("E4").End(xlDown)))
.NumberFormat = "#,##0"
.Font.Bold = True
End With

'Column I, PCs
With Range("I4").End(xlDown).Offset(2, 0)
.Value = WorksheetFunction.Sum(Range("I4", Range("I4").End(xlDown)))
.NumberFormat = "$#,##0.00"
.Font.Bold = True
End With
With Range("I4").End(xlDown).Offset(3, 0)
.Value = WorksheetFunction.CountA(Range("I4", Range("I4").End(xlDown)))
.NumberFormat = "#,##0"
.Font.Bold = True
End With

'Column J, Lost PCs
With Range("J4").End(xlDown).Offset(2, 0)
.Value = WorksheetFunction.Sum(Range("J4", Range("J4").End(xlDown)))
.NumberFormat = "$#,##0.00"
.Font.Bold = True
End With
With Range("J4").End(xlDown).Offset(3, 0)
.Value = WorksheetFunction.CountA(Range("J4", Range("J4").End(xlDown)))
.NumberFormat = "#,##0"
.Font.Bold = True
End With

End Sub
 

Forum statistics

Threads
1,144,151
Messages
5,722,801
Members
422,459
Latest member
Chriselff

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
Top