Create a macro to sum certain parts of a column

[Iggy 2]

Hi all I am back to be schooled on macros. I need help building a macro to use the sum formula on certain values separated by a blank row
The data looks like this.

9564.46
2315.15
84842.15
154.32

4844.185
751.15

1548.48
154.84
154.84

I don't know if there is an easy way to paste formulas in a way that it picks up weather to sum 1-5 lines of data. instead of copy pasting it and going back and editing every formula with the correct number of rows.

Thanks in advance

 

[Rick Rothstein]

Hi all I am back to be schooled on macros. I need help building a macro to use the sum formula on certain values separated by a blank row
The data looks like this.

9564.46
2315.15
84842.15
154.32

4844.185
751.15

1548.48
154.84
154.84

You did not say where you wanted the sums displayed, so I assumed next to the last cell in each group. You also did not say what column your data is in, so I assumed Column A. I also assumed your values were constants... if they are formulas, then change xlConstants to xlFormulas.

Sub SumGroupsOfNumbers()
  Dim Ar As Range
  For Each Ar In Columns("A").SpecialCells(xlConstants).Areas
    Ar(Ar.Count).Offset(, 1).Value = Application.Sum(Ar)
  Next
End Sub

 

[Iggy 2]

awesome thanks so what does Ar mean in this code?

 

[Rick Rothstein]

awesome thanks so what does Ar mean in this code?

Ar is simply a variable declared as a range... it could have been named anything but I chose Ar to act as a reminder to me that it was holding an Area of cells from within the full range of cells. The Columns("A").SpecialCells(xlConstants).Areas contains each group of constants in Column A as individual ranges (each contiguous range within a non-contiguous range of cells is called an Area), so the loop assigns each Area, one at a time, to the Ar variable and the code inside the loop processes the content of the currently iterated Area.

 

[Iggy 2]

Ok and if i want to display the total below I just switch next to below? and can i use EntireRow.Insert at the end to add a row after each total?

 

[Iggy 2]

Never mind about the Next question, i just read its for re looping the macro.

 

[Rick Rothstein]

Ok and if i want to display the total below I just switch next to below? and can i use EntireRow.Insert at the end to add a row after each total?

You would have to do what you are asking for like this...

Sub SumGroupsOfNumbers()
  Dim Ar As Range
  For Each Ar In Columns("A").SpecialCells(xlConstants).Areas
    Ar(Ar.Count).Offset(1).EntireRow.Insert
    Ar(Ar.Count + 1).Value = Application.Sum(Ar)
  Next
End Sub

 

[Iggy 2]

I see, i have to insert the row before making the sum?

 

[Iggy 2]

Thanks so much for your help

 

[Rick Rothstein]

I see, i have to insert the row before making the sum?

It can be done either way...

Sub SumGroupsOfNumbers()
  Dim Ar As Range
  For Each Ar In Columns("A").SpecialCells(xlConstants).Areas
    Ar(Ar.Count + 1).Value = Application.Sum(Ar)
    Ar(Ar.Count + 1).Offset(1).EntireRow.Insert
  Next
End Sub