|
|
|
|
|
|
|
|
|
Excel
|
Sums All Numeric Values in a Selection
|
|
|
Ease of Use
|
Easy
|
|
Version tested with
|
2000
|
|
Submitted by:
|
MWE
|
|
Description:
|
Sums all numerical values in a selected range, ignoring blank cells and cells with non-numeric data. MsgBox display is # cells evaluated, # blank cells, # bypassed cells (non-numeric data), contents of those cells, and actual sum
|
|
Discussion:
|
One often needs to sum a range of cells just to know what that sum is and not necessarily to record that sum as a documented part of the spreadsheet. For example, when checking financial data, you want to add a group of numbers and do not want to spend the time to build the formula to actually do that (and then delete the formula). The macro xlSumHiLited will sum any selected range of cells and output the sum. It will also tell you if any cells were blank or contained non-numeric data ? pretty useful when you are trying to figure out why your checkbook does not balance.
|
|
Code:
|
instructions for use
|
Option Explicit
Sub xlSumHiLited()
Dim Num As Integer, NumBad As Integer, NumBlank As Integer
Dim Cell As Range
Dim Total As Single
Dim strBuffer As String
Total = 0
Num = 0
NumBad = 0
NumBlank = 0
For Each Cell In Selection
On Error Resume Next
If Cell.Value <> "" Then
Total = Total + Cell.Value
Select Case Err
Case Is = 0
Num = Num + 1
Case Is <> 0
NumBad = NumBad + 1
strBuffer = strBuffer & " " & Cell.Value & vbCrLf
End Select
Else
NumBlank = NumBlank + 1
End If
Next Cell
Select Case NumBad
Case Is = 0
MsgBox "xlSumHiLited" & vbCrLf & vbCrLf & _
"# cells examined = " & (Num + NumBad + NumBlank) & vbCrLf & _
"# blank cells = " & NumBlank & vbCrLf & _
"# cells actually summed = " & Num & vbCrLf & vbCrLf & _
"SUMMED TOTAL = " & Total, vbInformation
Case Is > 0
MsgBox "xlSumHiLited" & vbCrLf & vbCrLf & _
"# cells examined = " & (Num + NumBad + NumBlank) & vbCrLf & _
"# blank cells = " & NumBlank & vbCrLf & _
"# cells actually summed = " & Num & vbCrLf & vbCrLf & _
"SUMMED TOTAL = " & Total & vbCrLf & vbCrLf & _
"# cells bypassed = " & NumBad & vbCrLf & _
"contents of bypassed cells:" & vbCrLf & _
strBuffer, vbInformation
End Select
End Sub
|
|
How to use:
|
- Copy the above code.
- Open any workbook.
- Press Alt + F11 to open the Visual Basic Editor (VBE).
- In the left side window, select the target spreadsheet [it will likely be called VBAProject(filename.xls) where filename is the name of the spreadsheet]
- Select an existing code module for the target worksheet; or from the Insert Menu, choose Insert | Module.
- Paste the code into the right-hand code window.
- Close the VBE, save the file if desired.
|
|
Test the code:
|
- Select a range of cells.
- Go to Tools | Macro | Macros and double-click on xlSumHiLited
|
|
Sample File:
|
xlSumHiLited.zip 9.88KB
|
|
Approved by mdmackillop
|
|
This entry has been viewed 203 times.
|
|
|