Copy Only Subtotals in Excel

If you have ever used Excel’s subtotals feature (Data | Subtotals) you know how handy it can be to summarize data. For example, LIMS’ example sample tracking templates use subtotals to create a concise sample backlog report.

But what do you do if you need to copy just the data in the subtotal rows on the workbook? An LIMS user posed that question, and we discovered that Excel’s “Visible cells only” option provides the solution. You can use this Excel feature in a one-time copy and when you want to automate the process in an export template’s AfterTransferFromLIMS macro. To see the problem and its solution, first preview a Sample Tracking report in the LIMS. (Hint: Use the “All” incomplete analyses option and check the “Incomplete samples” option on the Additional tab to query all incomplete analyses). Export the Sample Tracking report to one of the example sample tracking templates. Without expanding the outline on the Report worksheet, select all of the cells in the collapsed outline, then use Ctrl+C to copy the cells. Click a column A cell below the outline and use Ctrl+V to paste the copied data and you will see that all of the data including the hidden rows is copied. Use Edit | Undo Paste or Ctrl+Z to undo the paste.

Now reselect all of the cells in the collapsed outline, then use Edit | Go To…, click the [Special…] button and choose the “Visible cells only” option. Use Ctrl+C to copy the cells. Click a column A cell below the outline and use Ctrl+V to paste the copied data, and this time you will see that only the subtotal rows (i.e. visible rows) were copied.

It is also simple to perform this operation in a template’s macro. After macro code selects the outline cells to copy, the following VBA statement will change the selection to only the visible cells:

Selection.SpecialCells(xlCellTypeVisible)
.Select