# Need help with dynamic subtotal & row count

#### psulion01

##### Board Regular
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

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple
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

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)"

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

-bump-

Anyone handy with VBA care to offer some help?

On 2002-09-29 14:43, psulion01 wrote:
-bump-

Anyone handy with VBA care to offer some help?

- 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

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

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

Replies
1
Views
510
Replies
0
Views
519
Replies
6
Views
889
Replies
0
Views
883
Replies
0
Views
365

1,221,094
Messages
6,157,889
Members
451,448
Latest member
SAM1961

### 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.

### Which adblocker are you using?

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

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