VBA to Find Sum of Even Numbers and Odd Numbers

VBA to Find Sum of Even Numbers and Odd Numbers:
Question: In excel a column has odd and even numbers, I need to display the sum of odd numbers in particular cell and sum of even numbers in a particular cell….using macros
Answer:
You can use simple Excel formula to have the sum of Even or Odd Numbers in a cell.
Excel Formula to find Sum of Even Numbers and Odd Numbers
However, you can use VBA to sum EVEN or ODD numbers in a particular Column or Range. Here are example macro to find Sum of Even or Odd numbers in a given Column.

Steps to find Sum of Even Numbers using VBA

1. Decide the Column to Sum the Even Numbers
2. Loop through the each cell in the Column
3. Check if the cell value is Even Number or not
4. You can use ‘Cell Mod 2=0’ to check if Cell is Even Number
5. Add all Even numbers to a temporary variable
6. Finally print or show the Sum of Even numbers at required range

Excel VBA Macro to find Sum of Even Numbers

Here is the example macro to sum all Even values in a column:

How this works?

• LastRow = .Cells(.Rows.Count, “A”).End(xlUp).Row: We are finding the last row in a given Columns (A)
• totSum = 0 : Initiating the temporary variable (totSum) to store the sum of values
• For i = 1 To LastRow: Looping through the each cell in the given Column (A)
• If Range(“A” & i) Mod 2 = 0 : Checking if a cell value is Even
• Then totSum = totSum + Range(“A” & i): Summing to the temporary Variable
• Range(“B1”) = totSum: Printing the Sum of Even numbers at B1

Steps to find Sum of Odd Numbers using VBA

1. Decide the Column to Sum the Odd Numbers
2. Loop through the each cell in the Column
3. Check if the cell value is Odd Number or not
4. You can use ‘Cell Mod 2=1’ to check if Cell is Odd Number
5. Add all Odd numbers to a temporary variable
6. Finally print or show the Sum of Odd numbers at required range

VBA to find Sum of Odd Numbers

Here is the example macro to sum all Odd values in a column:

How this works?

• LastRow = .Cells(.Rows.Count, “A”).End(xlUp).Row: We are finding the last row in a given Columns (A)
• totSum = 0 : Initiating the temporary variable (totSum) to store the sum of values
• For i = 1 To LastRow: Looping through the each cell in the given Column (A)
• If Range(“A” & i) Mod 2 = 1 : Checking if a cell value is Odd
• Then totSum = totSum + Range(“A” & i): Summing to the temporary Variable
• Range(“B2”) = totSum: Printing the Sum of Odd numbers at B2

VBA to find Sum of Even and Odd Numbers

Here is the example macro to sum all Even and Odd values in a column:

How this works?

• LastRow = .Cells(.Rows.Count, “A”).End(xlUp).Row: We are finding the last row in a given Columns (A)
• totSumEven = 0 : Initiating the temporary variable (totSumEven ) to store the sum of Even values
• totSumOdd = 0 : Initiating the temporary variable (totSumOdd ) to store the sum of Odd values
• For i = 1 To LastRow: Looping through the each cell in the given Column (A)
• If Range(“A” & i) Mod 2 = 0 : Checking if a cell value is Even
• Then totSumEven = totSumEven + Range(“A” & i): Summing even numbers to the temporary Variable
• If Range(“A” & i) Mod 2 = 1 : Checking if a cell value is Odd
• Then totSumOdd = totSumOdd + Range(“A” & i): Summing Odd numbers to the temporary Variable
• Range(“B1”) = totSumEven : Printing the Sum of Even numbers at B1
• Range(“B2”) = totSumOdd : Printing the Sum of Odd numbers at B2

Instructions to Run VBA Macro Code or Procedure:

You can refer the following link for the step by step instructions.

Instructions to run VBA Macro Code

Other Useful Resources:

Click on the following links of the useful resources. These helps to learn and gain more knowledge.

VBA Tutorial VBA Functions List VBA Arrays VBA Text Files VBA Tables

VBA Editor Keyboard Shortcut Keys List VBA Interview Questions & Answers Blog