2

I'm trying to sum up a column in Excel VBA , however because there are blank spaces, I'm getting 0 as the sum.

Dim rExternalTotal As Range , dExternalTotal as Double
Set rExternalTotal = Range(rReportData.Offset(0, 0), rReportData.Offset(261, 0).End(xlUp))
dExternalTotal = Application.WorksheetFunction.Sum(rExternalTotal)

dExternalTotal is always zero. I cannot delete the blank spaces on the work sheet. Can anyone suggest a way to do the sum programatically?

Improve this question
3
  • Ugh, I liked the original post's format better. Just hate horizontal scroll bars... Commented Jun 2, 2010 at 19:05
  • rReportData is just another range Commented Jun 3, 2010 at 11:27
  • Add this before your Sum(), as a check: debug.print rExternalTotal.address Commented Aug 5, 2011 at 9:38

3 Answers 3

Reset to default
5

I think you are misinterpreting the source of the error; rExternalTotal appears to be equal to a single cell. rReportData.offset(0,0) is equal to rReportData
rReportData.offset(261,0).end(xlUp) is likely also equal to rReportData, as you offset by 261 rows and then use the .end(xlUp) function which selects the top of a contiguous data range.
If you are interested in the sum of just a column, you can just refer to the whole column:

dExternalTotal = Application.WorksheetFunction.Sum(columns("A:A"))

or

dExternalTotal = Application.WorksheetFunction.Sum(columns((rReportData.column))

The worksheet function sum will correctly ignore blank spaces.

Let me know if this helps!

Improve this answer
Sign up to request clarification or add additional context in comments.

5 Comments

Thanks for replying, however it's stil giving me zero as the sum total.
This one line of code, without any of the above works for me (with an additional line of code to initialize rReportData), I tested in on various columns; are you initializing rReportData? Can you post the full code, including where you display dExternalTotal?
sorry the code is too long to send. But rReportData is just a normal range. The data looks like this: COLUMN C <BR> 7,000,000.00 7,130,000.00 5,165,000.00 3,360,000.00 2,030,000.00 3,420,000.00 5,170,000.00 1,650,000.00
I'm not quite sure what the issue is; this runs fine for me. Are you sure that there isn't a typo when you are referencing dExternalTotal the second time (or wherever you display it)? Mistyping the variable name could result in VBA creating a new variable which would default to 0. Otherwise I'm somewhat at a loss. Does this code dExternalTotal = Application.WorksheetFunction.Sum(columns("A:A")) give you the correct sum from column A?
No it doesn't. The first 2 cells are blank and so are several cells in that column. I'm doing excactly the same thing for 3 other ranges and it works fine. i will retest your code again.
3

Here is what you can do if you want to add a column of numbers in Excel. ( I am using Excel 2010 but should not make a difference.)

Example: Lets say you want to add the cells in Column B form B10 to B100 & want the answer to be in cell X or be Variable X ( X can be any cell or any variable you create such as Dim X as integer, etc). Here is the code:

Range("B5") = "=SUM(B10:B100)"

or 

X = "=SUM(B10:B100)

There are no quotation marks inside the parentheses in "=Sum(B10:B100) but there are quotation marks inside the parentheses in Range("B5"). Also there is a space between the equals sign and the quotation to the right of it.

It will not matter if some cells are empty, it will simply see them as containing zeros!

This should do it for you!

Improve this answer

Comments

1

I have a label on my form receiving the sum of numbers from Column D in Sheet1. I am only interested in rows 2 to 50, you can use a row counter if your row count is dynamic. I have some blank entries as well in column D and they are ignored.

Me.lblRangeTotal = Application.WorksheetFunction.Sum(ThisWorkbook.Sheets("Sheet1").Range("D2:D50"))
Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.