Sum in first blank cell and a few more things...

[Bleu_808]

Hello all

I had raised a question a few days ago, but I still haven't figured out
a solution. I hope someone can help

My column J is a list of dollar amounts.

14.10
25.00
36.00
(blank cell) (want subtotal of previous 3 and make bold)
(blank cell)
65.00
23.00
59.65
(Blank cell) (want subtotal of previous 3 and make bold)
(blank cell)



I want to do three things:
1) sum each of the totals so that they appear in the 1st blanl cell
after the grouping.
2) Bold the subtotal.
3) Have each of the summed for a grand total.

This is a list that will have varying rows, so that last row with the
grand total will always be different.

Any macro that can do all of this for me?

Previously, someone suggested creating a new column k, and enter a
formula. That worked for the subtotals, but I was not able to get a
grand total of the subs. Also suggested was to use Autosum, but I
havent been able to determina a range that will work.

Thanks again for all of your help! Any suggestions are vey much
appreciated!

Bleu

 

[Dave O]

A non-macro solution:
Have you considered the SUBTOTAL function? The format is
=SUBTOTAL(9,A1:A10) (Note: the "9," is important.)
This provides the subtotal of the range you specify- and here's the
tricky bit- while ***ignoring*** cells in that range that also use the
SUBTOTAL formula. So if your column looked like (starting in A1, for
example)
14
25
36
subtotal formula
blank
15
26
37
48
subtotal formula
blank
=SUBTOTAL(9,A1:A11) would return 201 because it disregards the formulas
in cells A4 and A10.

To make this a quicker process, with the cell pointer in A4 press the
Sigma menu button, which automatically enters a SUM formula, and repeat
for other subtotal cells. Then search for the text string SUM( and
replace it with SUBTOTAL(9,

It all comes down to: did you request a macro in your original post
because you have to do this over a huge range of cells all day every
day, or because you thought there was no other way?

 

[Bleu_808]

Dave,

Thanks for the info. I didn't request a macro in the original post,
but really thought it would be easier. I have 15 different
spreadhseets that I will use this info on - macro would be one step.
Each are updated weekly, and vary in length, so assigning formula to
specific cells would mean a lot of work! I didn't even know if what I
was trying to do was possible.

Thanks again,

Bleu

 

[Dave O]

It is possible, because anything you can do manually with a keyboard or
mouse you can automate with VBA. The tricky part for you will be how
to tell Excel where to start a range and where to end a range. If your
column has blank rows in it already, you can get the macro to start at
the first non-blank cell, pick up the address, and step downward row by
row until a blank row is encountered, and write an appropriate formula
based on the current address. Do the rows have any other clues that
indicate where a range starts and stops? If yes, then your code can
use those as guidelines too.