If it's from a fixed row, eg 2, down to the row above where the sum formula will be you can use this.

```
Range("H2808:J2808").FormulaR1C1 = "=SUM(R2C:R[-1]C)"
Range("G2808").Value = "Totals"
Range("P2808:R2808").FormulaR1C1 = "=SUM(R2C:R[-1]C)"
```

Solved

Posted on 2011-10-20

Hello:

Attached is a file containing VBA macro scripting embedded in my Excel 2010 .xltm workbook.

The macro is working in that it is successfully summing the amounts in columns H, I, J, P, Q, and R.

But, it is only doing so up through row 2,807. (That's because, at this moment in time, my spreadsheet only contains 2,807 rows of data.) You see, I want it to sum the amounts in these six columns no matter what the number of rows is.

You see, next month when I run this macro for the data in my spreadsheet, the number of rows is not necessarily going to be 2,807. It may be 3,000 or 1,000 or 4,983, or whatever.

So, I want to know what syntax I need to put in the programming to conduct the totals "dynamically" for the amounts in these six columns.

Also, I need the word "Totals" to appear at the bottom of column G, which would be one column to the left of the total amount of column H of course. But, again, I want the word "Totals" to appear in column G at the "end" of the set of data--no matter how many rows there are.

From what I can tell, then, the following are the three "sets" of syntax in this macro that are going to need to be modified in order to accomplish this:

Range("H2808:J2808").Select

Selection.FormulaR1C1 = "=SUM(R[-2806]C:R[-1]C)"

Range("G2808").Select

ActiveCell.FormulaR1C1 = "Totals"

Range("G2808").Select

Range("P2808:R2808").Select

Selection.FormulaR1C1 = "=SUM(R[-2806]C:R[-1]C)"

Finally, once this syntax is changed to what I need, I plan on copying and pasting the new syntax into my .xltm file and saving it over the existing file. That is the correct way of saving such files. Isn't it?

Thanks! Much appreciated!

Apitech

Dynamics-Summing.txt

Attached is a file containing VBA macro scripting embedded in my Excel 2010 .xltm workbook.

The macro is working in that it is successfully summing the amounts in columns H, I, J, P, Q, and R.

But, it is only doing so up through row 2,807. (That's because, at this moment in time, my spreadsheet only contains 2,807 rows of data.) You see, I want it to sum the amounts in these six columns no matter what the number of rows is.

You see, next month when I run this macro for the data in my spreadsheet, the number of rows is not necessarily going to be 2,807. It may be 3,000 or 1,000 or 4,983, or whatever.

So, I want to know what syntax I need to put in the programming to conduct the totals "dynamically" for the amounts in these six columns.

Also, I need the word "Totals" to appear at the bottom of column G, which would be one column to the left of the total amount of column H of course. But, again, I want the word "Totals" to appear in column G at the "end" of the set of data--no matter how many rows there are.

From what I can tell, then, the following are the three "sets" of syntax in this macro that are going to need to be modified in order to accomplish this:

Range("H2808:J2808").Selec

Selection.FormulaR1C1 = "=SUM(R[-2806]C:R[-1]C)"

Range("G2808").Select

ActiveCell.FormulaR1C1 = "Totals"

Range("G2808").Select

Range("P2808:R2808").Selec

Selection.FormulaR1C1 = "=SUM(R[-2806]C:R[-1]C)"

Finally, once this syntax is changed to what I need, I plan on copying and pasting the new syntax into my .xltm file and saving it over the existing file. That is the correct way of saving such files. Isn't it?

Thanks! Much appreciated!

Apitech

Dynamics-Summing.txt

6 Comments

If it's from a fixed row, eg 2, down to the row above where the sum formula will be you can use this.

```
Range("H2808:J2808").FormulaR1C1 = "=SUM(R2C:R[-1]C)"
Range("G2808").Value = "Totals"
Range("P2808:R2808").FormulaR1C1 = "=SUM(R2C:R[-1]C)"
```

```
Dim rng As Range
Set rng = Range("H" & Rows.Count).End(xlUp).Offset(1)
rng.Resize(,3).FormulaR1C1 = "=SUM(R2C:R[-1]C)"
rng.Offset(,-1).Value = "Totals"
rng.Offset(,8).Resize(,3).FormulaR1C1 = "=SUM(R2C:R[-1]C)"
```

It finds the last row of data and puts the formula in the next row.

This was an Excel-based macro that I created and not proramming of code from scratch. I just want to modify the code that I have for each column, so that the macro dyamically sums the data and puts in the word "Totals".

```
Dim LastRowG As Long
Dim LastRowH As Long
Dim LastRowP As Long
LastRowG = Range("G" & Rows.Count).End(xlUp).Row
LastRowH = Range("H" & Rows.Count).End(xlUp).Row
LastRowP = Range("P" & Rows.Count).End(xlUp).Row
Range("H" & (LastRowH + 1) & ":J" & (LastRowH + 1)).Select
Selection.FormulaR1C1 = "=SUM(R[-" & (LastRowH - 1) & "]C:R[-1]C)"
Range("G" & (LastRowG + 1)).Select
ActiveCell.FormulaR1C1 = "Totals"
Range("G" & (LastRowG + 1)).Select
Range("P" & (LastRowH + 1) & ":R" & (LastRowH + 1)).Select
Selection.FormulaR1C1 = "=SUM(R[-" & (LastRowH - 1) & "]C:R[-1]C)"
```

By clicking you are agreeing to Experts Exchange's Terms of Use.

Title | # Comments | Views | Activity |
---|---|---|---|

Excel File Date Format | 6 | 16 | |

Excel 2010 question | 3 | 20 | |

Match Values and Return to Cell | 6 | 14 | |

MS Excel default colors in styles | 2 | 0 |

Join the community of 500,000 technology professionals and ask your questions.

Connect with top rated Experts

**14** Experts available now in Live!