PDA

View Full Version : [SOLVED] Macros to add rows and subtotals



asugianto
04-06-2005, 05:11 PM
Here is the sample of my excel data:
BETTY 20 HEW A 22
BETTY 20 HEW B 0
BETTY 20 HEW C 1
BETTY 20 HEW D 7
BETTY 20 HEW E 10
BETTY 20 HEW F 64
BETTY 20 HEW G 4
BETTY 23 THOMAS A 0
BETTY 23 THOMAS B 0
BETTY 23 THOMAS C 0
BETTY 23 THOMAS D 0
BETTY 23 THOMAS E 0
BETTY 23 THOMAS F 0
BETTY 23 THOMAS G 0

I would like to have a macro that automatically add rows for every data change in column 3 and add subtotals for column 5. Final data will look like this:
BETTY 20 HEW A 22
BETTY 20 HEW B 0
BETTY 20 HEW C 1
BETTY 20 HEW D 7
BETTY 20 HEW E 10
BETTY 20 HEW F 64
BETTY 20 HEW G 4
Subtotal 108

BETTY 23 THOMAS A 0
BETTY 23 THOMAS B 0
BETTY 23 THOMAS C 0
BETTY 23 THOMAS D 0
BETTY 23 THOMAS E 0
BETTY 23 THOMAS F 0
BETTY 23 THOMAS G 0
Subtotal 0

Thank you!

Alvin :hi:

Jacob Hilderbrand
04-06-2005, 05:31 PM
You can use Excel's Subtotal feature to do this for you.



Make sure the data has a header row


Select the data


From the Worksheet Menu Bar select Data


Select Subtotals...


At each change in: {Column C Header}


Use function: {Sum}


Add subtotal to: {Column E Header}


Ok

asugianto
04-06-2005, 06:24 PM
It works! Thanks! :thumb

Ken Puls
04-06-2005, 06:44 PM
Hi asugianto, and welcome to VBAX!Did you know that you can mark your own threads solved here? Just see the note in my signature.

I'll get this one for you! ;)

Jacob Hilderbrand
04-06-2005, 06:55 PM
You're Welcome :beerchug:

Take Care