subtotaling text question

[tralalah]

I've got a quick question. I am subtotaling an order qty report by items

Column a is the item numbers
column b is the order qty
column c is the product group- this is a text field
column d is the sub-product group- this is a text field

When I do the subtotal only 0 shows up for the product and sub-product. How can I subtotal and have those text fields show up on the total line?

Thanks for any light you can shed.

 

[pbornemeier]

Consider using a PivotTable to summarize your data. Column A, C & D to the Row Area and B to the Data Area - ensure the aggregation is Sum.

 

[tralalah]

Thanks but a pivot table is not really what I am looking for. I think the data range is too big for a pivot table it would be like 1000 lines long. I just simply want a way for the text to show up when the item is grouped instead of a 0.

 

[pbornemeier]

Try this code. It will work most quickly if only the subtotal rows are visible.

Sub InsertSubtotalTextValues()

    'This code will copy the value of the cell (or insert a formula pointing at the cell)
    'above a blank cell into the blank cell
    
    Dim rngUsed As Range
    Dim rngCell As Range
    
    Set rngUsed = Range(Cells(2, 1), Cells(ActiveCell.SpecialCells(xlLastCell).Row, 4))
    rngUsed.Select
    Set rngUsed = Selection.SpecialCells(xlCellTypeVisible)
    
    For Each rngCell In rngUsed
        'If rngCell = "" Then rngCell.FormulaR1C1 = "=R[-1]C"   'will put in a formula
        If rngCell = "" Then rngCell = rngCell.Offset(-1, 0)   'will put in a value
    Next
    
    Set rngCell = Nothing
    Set rngUsed = Nothing
End Sub

 

[pbornemeier]

Thanks - I just found the FAQ describing how to apply the code formatting!