Multiple autosums with different size datasets

[john69]

Hello all. I first must say...I am a VBA newbie so please bear with me, but would like some help maybe even modifying a macro. I also know that the macro recorder itself is not the right place for autosumming.
I work daily with a spreadsheet that's downloaded from my company web site that I have to manually sort and group, which is not the issue here, this unfortunately has to be manual, however I would like to be able to click on a certain cell in column M, press a macro shortcut and get an auto-sum for the continguous dataset above it plus a blank row for columns M, N, O, P, X & Z.
The number of rows will always be different and I will need to sum many groups on 1 page. I may have a group of 25 items (from row 2-26) and want my sum in the above listed columns on row 28, then another group from rows 30-37 and the sum in row 39, etc...I really hope this makes sense and thank you in advance for any much needed assistance..

Sample code from macro recorder of what I am trying to do:

Sub autosum()
ActiveCell.Range("A1:D1").Select
Selection.FormulaR1C1 = "=SUM(R[-15]C:R[-1]C)"
ActiveCell.Offset(0, 11).Range("A1:C1").Select
Selection.FormulaR1C1 = "=SUM(R[-15]C:R[-1]C)"
ActiveCell.Offset(5, -11).Range("A1:D1").Select
Selection.FormulaR1C1 = "=SUM(R[-3]C:R[-1]C)"
ActiveCell.Offset(0, 11).Range("A1:C1").Select
Selection.FormulaR1C1 = "=SUM(R[-3]C:R[-1]C)"
ActiveCell.Offset(4, -11).Range("A1:D1").Select
Selection.FormulaR1C1 = "=SUM(R[-2]C:R[-1]C)"
End Sub

 

[Fazza]

Hi, John

Please try this in a dummy file.

Suggest you load it in the personal macros (post if you want explanation of that) and assign it to a shortcut key.

To run, select a cell in the last row (any column). Such as, select a cell row 26 - make it the active cell. Use the short cut key to run the code.

Move down to another cell, again last row of the block. Use the short cut key again.

It may do what you want; it may not...

cheers, Fazza

With ActiveCell
  .Offset(1).Resize(3).EntireRow.Insert Shift:=xlDown
  Union(Cells(.Row + 2, "M").Resize(, 4), Cells(.Row + 2, "X"), Cells(.Row + 2, "Z")).FormulaR1C1 = "=SUM(R" & .End(xlUp).Row & "C:R" & .Row & "C)"
End With

 

[Fazza]

PS John

I made an assumption that might be wrong: that there will always be more than 1 row being summed.

This is used in the code where .End(xlUp) finds the first row of the block being summed.

This is fine when there are cells above the active cell, though when there is only one row it will give the wrong answer.

For example, if you total rows 2:26 and then have 3 rows to the next block, starting in row 30. If the next block is only one row, that is only row 30, then the code needs modification.

F

 

[john69]

Actually, this is perfect, seems to work on all but the single rows, but hey, who needs to add a total to one line of data anyway? THANK YOU SO MUCH!!

I should have also added in the original post about the formatting of the "total" row. Do you think that you could help me format this row with bold font and green color?

Thanks again for your help!!

John

 

[Fazza]

I can help with that. I've done the whole row, maybe not what you want...

With ActiveCell
  .Offset(1).Resize(3).EntireRow.Insert Shift:=xlDown
  Union(Cells(.Row + 2, "M").Resize(, 4), Cells(.Row + 2, "X"), Cells(.Row + 2, "Z")).FormulaR1C1 = "=SUM(R" & .End(xlUp).Row & "C:R" & .Row & "C)"
  With .Offset(2).EntireRow
    .Font.Bold = True
    .Interior.Color = vbGreen
  End With
End With

 

[john69]

Exactly the results I was looking for. THANK YOU AGAIN!!

John

 

[Fazza]

You're welcome, John. Season's greetings, Fazza