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

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

[vba] Excel VBA - Sum up a column

Examples related to vba

Examples related to excel