# Thread: Summing columns until all totalled

1. ## Summing columns until all totalled

Hi
I have been thrown a little challenge which is to sum variable cells at variable locations until all complete.

Background
A spreadsheet is created from our CRM database. It has 19 columns and an endless number of rows representing jobs carried out in specific areas for organisations and each group of jobs separated by a lank row.

The challenge
To add a new row after each block, sum columns 11, 12 & 13, copy these totals to columns 21, 22 & 23. Repeat for each of the 17 groups, total columns 21, 22 \$ 23

The logic I have worked out:
1. Work in column 1
2. Search down for next blank cell
3. Check if next cell down contains data, if yes then continue, else goto 5
4. Insert row above and move up 1 row
5. Move to column 11
6. Sum above for columns 11, 12 & 13
7. Copy sub totals in columns 11, 12 & 13 to columns 21, 22 & 23
8. Repeat until finished
9. Move down 1 row
10. Move to column 21
11. Sum above for columns 21, 22 & 23
12. End

I hope this makes sense! While I understand the logic the code is a little above me

2. Bonriki,

Hope this is what you are looking for, The code will find the blank row between blocks of jobs and add a new row. It will then total the values in columns 11, 12, and 13 for each job and place those values in the inserted row at columns 21, 22, and 23. Finally, it will vertically sum all the subtotals for each column at the bottom. I have place buttons on the sheet to start the code and reset the sheet. You can add any number of additional rows to the job blocks or add as many new blocks as you wish.

HTH,
Maud

totalsheet1.png

totalsheet2.png
Code:
```Public Sub TotalBlocks()
'SET VARIABLES
LastRow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row
'-----------------------------------------------------------------
'FIND STARTING ROW
Row = 1
If [a1] = "" Then Row = 2
'-----------------------------------------------------------------
'FIND NEXT AVAILABLE ROW AND SUM BLOCKS
For I = 1 To LastRow
AvailableRow = Range("A" & Row).End(xlDown).Row + 1
Rows(AvailableRow & ":" & AvailableRow).Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
Cells(AvailableRow, 20) = "Subtotal:"
Cells(AvailableRow, 21) = WorksheetFunction.Sum(Range(Cells(Row, 11), Cells(AvailableRow - 1, 11)))
Cells(AvailableRow, 22) = WorksheetFunction.Sum(Range(Cells(Row, 12), Cells(AvailableRow - 1, 12)))
Cells(AvailableRow, 23) = WorksheetFunction.Sum(Range(Cells(Row, 13), Cells(AvailableRow - 1, 13)))
Row = AvailableRow + 2
LastRow = LastRow + 1
If AvailableRow = LastRow Then Exit For
Next I
'-----------------------------------------------------------------
'SUM COLUMNS
Cells(LastRow + 1, 20) = "Total:"
Cells(LastRow + 1, 21) = WorksheetFunction.Sum(ActiveSheet.Columns(21))
Cells(LastRow + 1, 22) = WorksheetFunction.Sum(ActiveSheet.Columns(22))
Cells(LastRow + 1, 23) = WorksheetFunction.Sum(ActiveSheet.Columns(23))
End Sub```

3. Maud
Thanks for that exactly what I was looking for

4. Maud
I have been doing some testing and have found a few anomalies.
1. Routine not functioning as required or as your demo file runs. See images below
TotalBlocks.JPG

TotalBlocks2.JPG
The shots are taken at the top and near the bottom of spreadsheet
I didn't mention that the first row was column headings, not sure if that should make difference

2. Routine fails before totalling columns U, V & W
TotalBlocksFail.JPG

The output is direct from our CRM which names the program in column B and then repeats it for every organisation in column A.
It is interesting that code only inserts a blank line and totals the one organisation for some programs

5. Is it possible to see how the sheet was set up before the code was run? What was the error message you received? Will the first block always start on line 3?

Maud

6. The problem was detecting the next available blank line when only one job was in the block.

I think we've got it now!

Good Luck,
Maud

7. ## The Following User Says Thank You to Maudibe For This Useful Post:

bonriki (2013-11-10)

8. Maud hi
That's great.
As I stated earlier the first line is a heading line and routine now inserts a line under the heading and totals it. Just a bit annoying but doesn't affect the functionality.
FYI the transferred file has up to 3000 records with approx 75 programs and is used for forward planning, so very useful!

Thanks again

9. Oops!!
Just discovered your routine for the sub-totals actually accumulates the totals. Not really ideal
Accumulate.JPG

10. bonriki,

Wow, never realized that. I fixed the header problem and changed line number 7 from AvailableRow = 1 to AvailableRow = 3 where 3 should be the first line of the first block. I also added an additional variable, StartRow, to remember where each block started so that the subtotal will start from that point and not the top of the sheet. Hopefully, all the bugs have been worked out.

Maud

11. ## The Following User Says Thank You to Maudibe For This Useful Post:

bonriki (2013-11-11)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•