[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 435
  • Last Modified:

Excel VBA Macro Summing No Matter How Many Rows

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
0
apitech
Asked:
apitech
  • 4
  • 2
1 Solution
 
NorieCommented:
What rows do you want to sum?

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)"

Open in new window

0
 
NorieCommented:
Oh, did you also want to change the code to put the formulas in the right row?
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)"

Open in new window

0
 
apitechAuthor Commented:
I think that there may be some confusion as to what I want.  Row 2808, again, is irrelevant as are the number of rows.  I want the summing accomplished for those six columns that I mentioned--no matter how many rows there are in those columns.  Likewise, I want the word "Totals" at the bottom of column G--no matter how many records of data are above it.

0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
NorieCommented:
The 2nd code I posted has no reference to row 2808.

It finds the last row of data and puts the formula in the next row.
0
 
apitechAuthor Commented:
What about the other columns?  I only see column H, in that code that was posted.

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".
0
 
NorieCommented:
Here you are:
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)"

Open in new window

 
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now