Contents
Sometimes it takes a very long time to come up with some things. But when they have ALREADY been invented, after the fact they seem obvious and even banal. From the series “what, it was possible?”.
From the very first versions, the status bar at the bottom of the Microsoft Excel window traditionally displayed totals for selected cells:
If desired, it was even possible to right-click on these results and select from the context menu exactly which functions we want to see:
And just recently, in the latest Excel updates, Microsoft developers added a simple but ingenious feature – now when you click on these results, they are copied to the clipboard!
Beauty.
But what about those who do not yet (or already?) have such a version of Excel? This is where simple macros can help.
Copying the sum of selected cells to the Clipboard using a macro
Open in tab developer (Developer) editor Visual Basic or use this keyboard shortcut Alt+F11. Insert new empty module via menu Insert – Module and copy the following code there:
Sub SumSelected() If TypeName(Selection) <> "Range" Then Exit Sub With GetObject("New:{1C3B4210-F441-11CE-B9EA-00AA006B1A69}") .SetText WorksheetFunction.Sum(Selection) .PutInClipboard End With End Sub Its logic is simple:
- First comes the “protection from the fool” – we check what exactly is highlighted. If not cells are selected (but, for example, a chart), then exit the macro.
- Then using the command Getobject we create a new data object where our sum of selected cells will be stored later. A long and incomprehensible alphanumeric code is, in fact, a link to the Windows registry branch where the library is located Microsoft Forms 2.0 Object Library, which can create such objects. Sometimes this trick is also called implicit late binding. If you do not use it, then you would have to make a link to this library in the file through the menu Tools — References.
- The sum of the selected cells is considered a command WorksheetFunction.Sum(Selection), and then the resulting amount is placed on the clipboard with the command PutInClipboard
For ease of use, you can, of course, assign this macro to a keyboard shortcut using the button Macros tab developer (Developer — Macros).
And if you want to see what exactly was copied after running the macro, you can turn on the Clipboard panel using the small arrow in the lower right corner of the corresponding group on The main (Home) tab:
Not only the amount
If, in addition to the banal amount, you want something else, then you can use any of the functions that the object provides us WorksheetFunction:
For example, there is:
- Sum – sum
- Average – arithmetic mean
- Count – number of cells with numbers
- CountA – number of filled cells
- CountBlank – number of empty cells
- Min – minimum value
- Max – maximum value
- Median – median (central value)
- … etc.
What if rows or columns are hidden (manually or by a filter) in the selected range? In order not to take them into account in the totals, we will need to slightly modify our code by adding to the object Selection property SpecialCells(xlCellTypeVisible):
Sub SumVisible() If TypeName(Selection) <> "Range" Then Exit Sub With GetObject("New:{1C3B4210-F441-11CE-B9EA-00AA006B1A69}") .SetText WorksheetFunction.Sum(Selection.SpecialCells(xlCellTypeVisible)) .PutInClipboard End With End Sub In this case, the calculation of any total function will be applied to visible cells only.
If you need a living formula
If you dream up, you can come up with scenarios when it is better to copy not a number (constant), but a living formula into the buffer, which calculates the totals we need for the selected cells. In this case, you will have to glue the formula from fragments, adding to it the removal of dollar signs and replacing the comma (which is used as a separator between addresses of several selected ranges in VBA) with a semicolon:
Sub SumFormula() If TypeName(Selection) <> "Range" Then Exit Sub With GetObject("New:{1C3B4210-F441-11CE-B9EA-00AA006B1A69}") .SetText "=СУММ(" & Replace(Replace(Selection.Address, ",", ";"), "$", "") & ")" .PutInClipboard End With End Sub Summation with additional conditions
And, finally, for completely maniacs, you can write a macro that will sum up not all the selected cells, but only those that satisfy the given conditions. So, for example, a macro will look like that puts the sum of selected cells into the Buffer, if their values are greater than 5 and at the same time they are filled with any color:
Sub CustomCalc() Dim myRange As Range If TypeName(Selection) <> "Range" Then Exit Sub For Each cell In Selection If cell.Value > 5 And cell.Interior.ColorIndex <> xlNone Then If myRange Is Nothing Then Set myRange = cell Else Set myRange = Union(myRange, cell) End If End If Next cell With GetObject("New:{1C3B4210-F441-11CE-B9EA-00AA006B1A69}") .SetText WorksheetFunction.Sum(myRange) .PutInClipboard End With End Sub As you can easily imagine, conditions can be set absolutely any – up to cell formats – and in any quantity (including by linking them together with logical operators or or and). There is a lot of room for imagination.
- Convert formulas to values (6 ways)
- What are macros, how to use them, where to insert Visual Basic code
- Useful information in the status bar of Microsoft Excel