Average if on dynamic Range

rex759

Well-known Member
Joined
Nov 8, 2004
Messages
608
Office Version
  1. 365
Platform
  1. Windows
Hello,
I have a worksheet that I want to add an average row after each category. I can get the added row but how do write a formula to select the changing ranges for each category?

The example shows the original data and what is should look like after the macro runs.
Excel Workbook
AB
1CategoryPrice
2Electric123
3Electric145
4Electric62
5Electric39
6Landscape47
7Landscape921
8Plumb823
9Plumb333
10Plumb90
Sheet1
Excel 2010
Excel Workbook
AB
1CategoryPrice
2Electric123
3Electric145
4Electric62
5Electric39
6Average92.25
7Landscape47
8Landscape921
9Average484.00
10Plumb823
11Plumb333
12Plumb90
13Average415.33
Sheet1
Excel 2010
Cell Formulas
RangeFormula
B6=SUBTOTAL(1,B2:B5)
B9=SUBTOTAL(1,B7:B8)
B13=SUBTOTAL(1,B10:B12)


Here is a snippet of the code:
Code:
Sub test()
lasta = Worksheets("Sheet2").Range("A65536").End(xlUp).Row
Set WorkRng = Range("a2:a" & lasta)

Application.ScreenUpdating = False

For i = WorkRng.Rows.Count To 2 Step -1
    If WorkRng.Cells(i, 1).Value<> WorkRng.Cells(i - 1, 1).Value Then
        WorkRng.Cells(i, 1).EntireRow.Insert
        'add total line header
        WorkRng.Cells(i, 1) = "Average " & WorkRng.Cells(i, 1).Offset(-1, 0)
       'add averages
         WorkRng.Cells(i, 1).Offset(0, 1) = "average formula"
    End If
Next

End Sub

Any help is appreciated.
 

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)
How about
Rich (BB code):
Sub rex759()
    Dim UsdRws As Long, i As Long
    Dim Rng As Range
    
Application.ScreenUpdating = False
    With Sheets("Sheet2")
        UsdRws = .Range("A" & Rows.Count).End(xlUp).Row
        With .Range("A" & UsdRws)
            .Offset(1).Value = "Average " & .Value
        End With
        For i = UsdRws To 3 Step -1
            If .Cells(i, 1).Value <> .Cells(i - 1, 1).Value Then
                .Cells(i, 1).EntireRow.Insert
                .Cells(i, 1) = "Average " & .Cells(i, 1).Offset(-1, 0)
            End If
        Next i
        For Each Rng In .Range("B:B").SpecialCells(xlConstants).Areas
            Rng.Offset(Rng.Count)(1).Formula = "=subtotal(1," & Rng.Address & ")"
        Next Rng
    End With
End Sub
 
Upvote 0
ahh, didn't think to do it separately or come up with any of that. It works perfectly. Thank you
 
Upvote 0
Glad to help & thanks for the feedback
 
Upvote 0
Question: the spreadsheet has more columns that I want to add the sub total line. I know I can copy the code below and change the Range from B:B, to C:C and D:D, etc.
Code:
For Each Rng In .Range("B:B").SpecialCells(xlConstants).Areas
            Rng.Offset(Rng.Count)(1).Formula = "=subtotal(1," & Rng.Address & ")"
        Next Rng

Is there a better way to accomplish this?
 
Upvote 0
How about
Code:
        For Each Rng In .Range("B:B").SpecialCells(xlConstants).Areas
            With Rng.Offset(Rng.Count)(1)
                .Formula = "=subtotal(1," & Rng.Address & ")"
                .Offset(, 1).Formula = "=subtotal(1," & Rng.Offset(, 1).Address & ")"
                .Offset(, 2).Formula = "=subtotal(1," & Rng.Offset(, 2).Address & ")"
            End With
        Next Rng
 
Upvote 0

Forum statistics

Threads
1,213,520
Messages
6,114,099
Members
448,548
Latest member
harryls

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top