Excel VBA - Sum up a column

Question

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

User · Answer

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!

User · Answer

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  amp  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

User · Answer

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

[vba] Excel VBA - Sum up a column

Examples related to vba

Examples related to excel