Sort out list and sum each different category

errtu

Board Regular
Joined
Sep 23, 2010
Messages
134
I have a list of commodities and each one has a number. This list is all mixed up. The macro should sort them out, put all the commodities that a re the same together and sum their numbers. So , I'll end up with a sorted out list with totals for each commodity.:confused:

maybe move them to a designated pair of columns for each commodity?
 
Last edited:

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)
Hello errtu,

Have you tried using the SubTotals... under Data on the main menu? This will automatically sort the commodities alphabetically and provides totals for each.
 
Upvote 0
Hello errtu,

Have you tried using the SubTotals... under Data on the main menu? This will automatically sort the commodities alphabetically and provides totals for each.
ok, i'm trying:

Rich (BB code):
 Private Sub PrintRoutine_Click()
Application.ScreenUpdating = False
 Columns("C:C").Select
    ActiveWorkbook.Worksheets("DDE").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("DDE").Sort.SortFields.Add Key:=Range("C2:C100"), _
        SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("DDE").Sort
        .SetRange Range("A1:D100")
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With

Sheets("DDE").Range("a1:d100").Select
    Selection.Subtotal GroupBy:=3, Function:=xlSum, TotalList:=Array(1), _
        Replace:=True, PageBreaks:=False, SummaryBelowData:=True


Application.ScreenUpdating = True
        
    End Sub
I get run time error 1004
select method o range class failed.

I am trying to make this happen while i am in another worksheet.
 
Last edited:
Upvote 0
Hello errtu,

It is rare in VBA that you will ever need to select an object before performing an action on it. Try this method insted:
Rich (BB code):
With Worksheets("DDE").Range("a1:d100")
    .Subtotal GroupBy:=3, Function:=xlSum, TotalList:=Array(1), _
     Replace:=True, PageBreaks:=False, SummaryBelowData:=True
End With
 
Upvote 0
Hello errtu,

It is rare in VBA that you will ever need to select an object before performing an action on it. Try this method insted:
Rich (BB code):
With Worksheets("DDE").Range("a1:d100")
    .Subtotal GroupBy:=3, Function:=xlSum, TotalList:=Array(1), _
     Replace:=True, PageBreaks:=False, SummaryBelowData:=True
End With

thanks for the tip
 
Upvote 0

Forum statistics

Threads
1,224,502
Messages
6,179,126
Members
452,890
Latest member
Nikhil Ramesh

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