AutoSum Cells above Active Cell and other stuff...

[railanc4309]

Hi All,

After doing a fair bit of 'googling' on this, i'm still really none-the-wiser on how to do what I want to do... So here I am!

I have a spreadsheet that contains several hundred rows of information like the example below:

Please note - these images are an example only, but the idea of what I want to do is still the same...

Where the rows contain the words 'double', I want to merge those two rows together (sum the numbers, and just keep one of the rows containing the ID, name and date etc.)

So, I want to position my cursor on cell A3 and insert a new row below the active cell.
I want to sum the numbers 2 cells above the active rows D2:D3, E2:E3, F2:F3 etc...) I also want to copy the other cells from the row above and bring them to be on the same line as my sums. (Highlighted in Yellow here to show you what I mean,)

So this bit needs to be fluid - in that wherever I position my cursor it will insert a row, sum 2 cells above (2 cells only!), and copy other lines from the row above and bring them down...

Once i've done that, I want to copy/paste as values so my sum's are now gone, and then remove the 'double' rows...

Finally, remove all blank spaces, and I'm left with a finished sheet of merged data.

In my 'real' spreadsheet, the columns aren't the same, I've just used these an example, but I'm hoping I can work out the rest once I've seen a bit of code...

I've got as far as inserting a line below the active cell, and I thought I had a bit of script that performed a 'sum' on the two cells above it, but alas - it's just not doing it!!

Any information anyone could give me would be really greatly appreciated... At the moment, i'm manually doing this work in my macros, which means i'm repeating the actions that I KNOW will be possible to do within another macro - I just don't know how!

Many thanks in advance,

Rachael

 

[GlennUK]

A hint would be to put the sum of the values directly into the cell, rather than creating a formula in the new row ... as you will be deleting the contributory cells anyway. Like :

cell_for_sum.Value=cell_for_sum.Offset(0,-1).Value + cell_for_sum.Offset(0,-2).Value

replacing cell_for_sum with whatever expression or object you have to reference the cell where the sum is to go.