Conditional Subtotal Macro

  • Thread starter Thread starter Jason Edwards
  • Start date Start date
J

Jason Edwards

I would like to create a macro to subtotal only data that is duplicated.
For instance, the current subtotal function subtotals all lines, even those
with 1 line item. Sorry if this is confusing, maybe the example below will
help:

1 2
2 1
2 1

The current subtotal function, if applied above will do the following:

1 2
1 subtotal 2
2 1
2 1
2 subtotal 2

I would like for it to skip subtotaling if there is only one item and only
do it for duplicate items therefore resulting in:

1 2
2 1
2 1
2 subtotal 2

Any help you can provide is much appreciated.
 
You might let Excel create the subtotals its way and then clear out the
subtotals you do not want. For example, with the column of data and
subtotals selected:

Sub a()
Dim Cell As Range
For Each Cell In Selection.SpecialCells(xlCellTypeFormulas)
If Cell.DirectPrecedents.Rows.Count = 1 Then
'Cell.EntireRow.Delete
''or
Cell.EntireRow.Clear
End If
Next
End Sub
 
Back
Top