insert row with total where criteria count ends

Question

i have excel sheet with 5 columns. i used following code for sorting. it works fine

lrow As Long

lrow = Application.WorksheetFunction.CountA(Sheet2.Range("A:A"))

Sheet2.Range("A3:E" & lrow).Sort key1:=Range("E3:E" & lrow), order1:=xlAscending, Header:=xlNo  ' sno column

my data look like this...

Refer to the image.
In the Mode column, there are seven 1's. I would like insert a row after the last one and calculate the total (arrow mark in the image).

Would you want a subtotal adding after the 2's and any other numbers that appear? — Darren Bartrup-Cook
– Darren Bartrup-Cook, Commented Nov 25 at 11:59
If you don't need to add a row, you could put the subtotal in a column to the right of the "Mode" column with a formula — cybernetic.nomad
– cybernetic.nomad, Commented Nov 25 at 14:31

Black cat · Accepted Answer · 2025-11-25 17:44:04Z

1

This snippet is a general sort and subtotal on a range on a worksheet. Assumes that the data is on the sheet in range A1:B15 with headers.

The sample data

A	B
HeaderA	HeaderB
1	34
1	43
1	5
2	5
2	67
2	56
2	56
2	2
1	6
1	54
1	2
1	5
1	7
1	5

Sub Subtot()

With Me.Sort
    .Header = xlYes
    .SortFields.Clear
    .SetRange Range("A1").CurrentRegion
    .SortFields.Add .Rng.Offset(1, 0).Resize(.Rng.Rows.Count - 1).Columns(1), xlSortOnValues, xlAscending
    .Apply
End With
Range("A1").CurrentRegion.Subtotal 1, xlSum, Array(2)
Range("A1").CurrentRegion.Rows.Ungroup
Range("A1").CurrentRegion.Rows.Ungroup
End Sub

The code uses the built-in SORT and SUBTOTAL functions.

After the execution

A	B
HeaderA	HeaderB
1	34
1	43
1	5
1	6
1	54
1	2
1	5
1	7
1	5
1 Total	161
2	5
2	67
2	56
2	56
2	2
2 Total	186
Grand Total	347

edited Nov 25 at 17:44

answered Nov 25 at 14:38

Black cat

7,6358 gold badges67 silver badges99 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Raja V 2 days ago

Black cat@ it works fine ... great

Collectives™ on Stack Overflow

insert row with total where criteria count ends

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related