Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Subtotal multiple columns and ...
This is my current code:
Sub subTotal() Dim FirstRow As Long Dim LastRow As Long Dim iRow As Long Dim botCell As Range Dim topCell As Range Dim wks As Worksheet Set wks = ActiveSheet With wks FirstRow = 2 .Rows(FirstRow).Insert .Cells(FirstRow, "A").Value = "dummyVal" LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row Set topCell = .Cells(LastRow, "A") Set botCell = .Cells(LastRow, "A") For iRow = LastRow To FirstRow + 1 Step -1 If .Cells(iRow, "A").Value = .Cells(iRow - 1, "A").Value Then Set topCell = .Cells(iRow - 1, "A") Else botCell.Offset(1, 0).EntireRow.Insert botCell.Offset(1, 1).Formula _ = "=subtotal(9," & topCell.Offset(0, 1).Address(0, 0) _ & ":" & botCell.Offset(0, 1).Address(0, 0) & ")" botCell.Offset(1, 0).Value = "Subtotal: " & botCell.Value Set botCell = .Cells(iRow - 1, "A") Set topCell = .Cells(iRow - 1, "A") End If Next iRow .Rows(FirstRow).Delete End With End Sub With the above this, I will need to modify to make the following happen: 1. I will need to total Columns D - H for each group. (need loop through an array, I suppose). 2. On Column I and Column J, the value is unique for each group. What I need to do is to carry that value to the subtotal row and blank out the rows that are not subtotal. I appreciate your help. Faye Larson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Subtotal Multiple Columns | Excel Worksheet Functions | |||
subtotal on 2 columns | Excel Discussion (Misc queries) | |||
Subtotal multiple columns | Excel Programming | |||
Subtotal by two columns at once | Excel Discussion (Misc queries) | |||
Subtotal and Total Columns | Excel Programming |