a macro button for sorting and subtotaling rows of data...(# of rows unknown)

R

rmarenyi

New Member
Joined
Nov 12, 2017
Messages
3
Hi,
I was previously given some code that would take a list and sort it by check # and then subtotal amounts by check # with a grand total at the end. The code was associated with a 'button' on the page to click whenever you wanted to create the summation page.
When I was given the code I must not of specified that the rows are not static (although the columns are) so when I ran this for a large number of items it appears to only sort the first 9 or so lines and then commence to subtotal by check #.

I need the code to sort the complete list (minus the row of column titles) given that the length of the list is unknown.

Here's what the page looks like where A1 through G1 are the column headings. Data begins with A2 and goes through G2. The row count are infinite and different each time.

[TABLE="width: 759"]
<colgroup><col width="69" style="width: 69pt;"><col width="69" style="width: 69pt;"><col width="69" style="width: 69pt;"><col width="69" style="width: 69pt;"><col width="69" style="width: 69pt;"><col width="69" style="width: 69pt;"><col width="69" style="width: 69pt;"><col width="69" span="4" style="width: 69pt;"></colgroup><tbody>[TR]
[TD="class: xl69, width: 69"]LOT[/TD]
[TD="class: xl69, width: 69"]Description[/TD]
[TD="class: xl70, width: 69"]QTY[/TD]
[TD="class: xl71, width: 69"]Client Share[/TD]
[TD="class: xl72, width: 69"]PAID[/TD]
[TD="class: xl73, width: 69"]DATE PAID[/TD]
[TD="class: xl72, width: 69"] CHECK #[/TD]
[TD="width: 69, align: left"]<img width="136" height="42" alt="Create Client Summary">

<tbody>
[TD="class: xl65"][/TD]

</tbody>
[/TD]
[TD="class: xl65, width: 69"][/TD]
[TD="class: xl65, width: 69"][/TD]
[TD="class: xl65, width: 69"][/TD]
[/TR]
[TR]
[TD="class: xl66"]x[/TD]
[TD="class: xl75, width: 69"]mirror ornate[/TD]
[TD="class: xl74"][/TD]
[TD="class: xl64, align: right"]40.00[/TD]
[TD="class: xl66"] y[/TD]
[TD="class: xl67, align: right"]6/19/18[/TD]
[TD="class: xl66"] 1169[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl66"]x[/TD]
[TD="class: xl75, width: 69"]gold mirror oval[/TD]
[TD="class: xl74"][/TD]
[TD="class: xl64, align: right"]15.00[/TD]
[TD="class: xl66"] y[/TD]
[TD="class: xl67, align: right"]4/30/18[/TD]
[TD="class: xl66"] 1129[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl66"]x[/TD]
[TD="class: xl75, width: 69"]round table gold grim[/TD]
[TD="class: xl74"][/TD]
[TD="class: xl64, align: right"]50.00[/TD]
[TD="class: xl66"] y[/TD]
[TD="class: xl67, align: right"]6/19/18[/TD]
[TD="class: xl66"] 1169[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl66"]x[/TD]
[TD="class: xl75, width: 69"]stork[/TD]
[TD="class: xl74"][/TD]
[TD="class: xl64, align: right"]62.50[/TD]
[TD="class: xl66"] y[/TD]
[TD="class: xl67, align: right"]6/19/18[/TD]
[TD="class: xl66"] 1169[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl66"]x[/TD]
[TD="class: xl75, width: 69"]gold mirrored candle holders[/TD]
[TD="class: xl74, align: right"]2[/TD]
[TD="class: xl64, align: right"]50.00[/TD]
[TD="class: xl66"] y[/TD]
[TD="class: xl67, align: right"]4/30/18[/TD]
[TD="class: xl66"] 1129[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl66"]x[/TD]
[TD="class: xl75, width: 69"]caned vanity chair[/TD]
[TD="class: xl74"][/TD]
[TD="class: xl64, align: right"]25.00[/TD]
[TD="class: xl66"] y[/TD]
[TD="class: xl67, align: right"]6/19/18[/TD]
[TD="class: xl66"] 1169[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl66"]x[/TD]
[TD="class: xl75, width: 69"]demilune table[/TD]
[TD="class: xl74"][/TD]
[TD="class: xl64, align: right"]100.00[/TD]
[TD="class: xl66"] y[/TD]
[TD="class: xl67, align: right"]4/30/18[/TD]
[TD="class: xl66"] 1129[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl66"]x[/TD]
[TD="class: xl75, width: 69"]NM mirrors[/TD]
[TD="class: xl74, align: right"]3[/TD]
[TD="class: xl64, align: right"]55.00[/TD]
[TD="class: xl66"] y[/TD]
[TD="class: xl67, align: right"]6/19/18[/TD]
[TD="class: xl66"] 1169[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl66"]x[/TD]
[TD="class: xl75, width: 69"]drop down small desk[/TD]
[TD="class: xl74"][/TD]
[TD="class: xl76, align: right"]62.50[/TD]
[TD="class: xl66"] y[/TD]
[TD="class: xl67, align: right"]4/30/18[/TD]
[TD="class: xl66"] 1129[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl66"]x[/TD]
[TD="class: xl75, width: 69"]RECORD PLAYER[/TD]
[TD="class: xl74"][/TD]
[TD="class: xl64, align: right"]50.00[/TD]
[TD="class: xl66"] y[/TD]
[TD="class: xl67, align: right"]4/30/18[/TD]
[TD="class: xl66"] 1129[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl66"]x[/TD]
[TD="class: xl75, width: 69"]side table w/glass[/TD]
[TD="class: xl74"][/TD]
[TD="class: xl64, align: right"]25.00[/TD]
[TD="class: xl66"] y[/TD]
[TD="class: xl67, align: right"]6/19/18[/TD]
[TD="class: xl66"] 1169[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl66"]x[/TD]
[TD="class: xl75, width: 69"]sunflower oil painting[/TD]
[TD="class: xl74"][/TD]
[TD="class: xl64, align: right"]50.00[/TD]
[TD="class: xl66"] y[/TD]
[TD="class: xl67, align: right"]6/19/18[/TD]
[TD="class: xl66"] 1169[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl66"]x[/TD]
[TD="class: xl75, width: 69"]KJV bible[/TD]
[TD="class: xl74"][/TD]
[TD="class: xl64, align: right"]37.50[/TD]
[TD="class: xl66"] y[/TD]
[TD="class: xl67, align: right"]6/19/18[/TD]
[TD="class: xl66"] 1169[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl66"]x[/TD]
[TD="class: xl75, width: 69"]TK Beside Still Waters[/TD]
[TD="class: xl74"][/TD]
[TD="class: xl64, align: right"]32.50[/TD]
[TD="class: xl66"] y[/TD]
[TD="class: xl67, align: right"]6/19/18[/TD]
[TD="class: xl66"] 1169[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl66"]x[/TD]
[TD="class: xl75, width: 69"]TK Light of Peace[/TD]
[TD="class: xl74"][/TD]
[TD="class: xl64, align: right"]75.00[/TD]
[TD="class: xl66"] y[/TD]
[TD="class: xl67, align: right"]4/30/18[/TD]
[TD="class: xl66"] 1129[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl66"]x[/TD]
[TD="class: xl75, width: 69"]calligraphy michael podesta[/TD]
[TD="class: xl74"][/TD]
[TD="class: xl64, align: right"]20.60[/TD]
[TD="class: xl66"] y[/TD]
[TD="class: xl67, align: right"]6/19/18[/TD]
[TD="class: xl66"] 1169[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl66"]x[/TD]
[TD="class: xl75, width: 69"]Sunflowers on canvas[/TD]
[TD="class: xl74"][/TD]
[TD="class: xl64, align: right"]30.00[/TD]
[TD="class: xl66"] y[/TD]
[TD="class: xl67, align: right"]4/30/18[/TD]
[TD="class: xl66"] 1129[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl66"]x[/TD]
[TD="class: xl75, width: 69"]oil paintings whimsical instruments[/TD]
[TD="class: xl74, align: right"]2[/TD]
[TD="class: xl64, align: right"]100.00[/TD]
[TD="class: xl66"] y[/TD]
[TD="class: xl67, align: right"]4/30/18[/TD]
[TD="class: xl66"] 1129[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl66"]x[/TD]
[TD="class: xl75, width: 69"]coral silk french chairs[/TD]
[TD="class: xl74, align: right"]2[/TD]
[TD="class: xl76, align: right"]225.00[/TD]
[TD="class: xl66"] y[/TD]
[TD="class: xl67, align: right"]6/19/18[/TD]
[TD="class: xl66"] 1169[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl66"]x[/TD]
[TD="class: xl75, width: 69"]ice cream chairs[/TD]
[TD="class: xl74, align: right"]2[/TD]
[TD="class: xl64, align: right"]150.00[/TD]
[TD="class: xl66"] y[/TD]
[TD="class: xl67, align: right"]6/19/18[/TD]
[TD="class: xl66"] 1169[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl66"]x[/TD]
[TD="class: xl75, width: 69"]red velvet vanity chair[/TD]
[TD="class: xl74"][/TD]
[TD="class: xl76, align: right"]40.00[/TD]
[TD="class: xl66"] y[/TD]
[TD="class: xl67, align: right"]6/19/18[/TD]
[TD="class: xl66"] 1169[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl66"]x[/TD]
[TD="class: xl75, width: 69"]gold chair pink seat[/TD]
[TD="class: xl74"][/TD]
[TD="class: xl64, align: right"]17.50[/TD]
[TD="class: xl66"] y[/TD]
[TD="class: xl77, align: right"]7/20/18[/TD]
[TD="class: xl78"] 1186[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl66"]x[/TD]
[TD="class: xl75, width: 69"]gold marble pedestal table[/TD]
[TD="class: xl74"][/TD]
[TD="class: xl64, align: right"]52.92[/TD]
[TD="class: xl66"] y[/TD]
[TD="class: xl67, align: right"]7/20/18[/TD]
[TD="class: xl66"] 1186[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl66"][/TD]
[TD="class: xl75, width: 69"]cherry side table[/TD]
[TD="class: xl74"][/TD]
[TD="class: xl76, align: right"]60.00[/TD]
[TD="class: xl66"] y[/TD]
[TD="class: xl67, align: right"]6/19/18[/TD]
[TD="class: xl66"] 1169[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl66"]x[/TD]
[TD="class: xl75, width: 69"]velvet stools[/TD]
[TD="class: xl74, align: right"]2[/TD]
[TD="class: xl64, align: right"]25.00[/TD]
[TD="class: xl66"] y[/TD]
[TD="class: xl67, align: right"]7/20/18[/TD]
[TD="class: xl66"] 1186[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl66"]x[/TD]
[TD="class: xl75, width: 69"]side table tile top[/TD]
[TD="class: xl74"][/TD]
[TD="class: xl76, align: right"]50.00[/TD]
[TD="class: xl66"] y[/TD]
[TD="class: xl67, align: right"]6/19/18[/TD]
[TD="class: xl66"] 1169[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl66"]x[/TD]
[TD="class: xl75, width: 69"]child rolltop desk[/TD]
[TD="class: xl74"][/TD]
[TD="class: xl64, align: right"]32.50[/TD]
[TD="class: xl66"] y[/TD]
[TD="class: xl67, align: right"]7/20/18[/TD]
[TD="class: xl66"] 1186[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Here is the Code I was provided with for the worksheet CLIENT LOG. The macro is SUBTOTAL2

Code: 
Sub SUBTOTAL2()'
' SUBTOTAL2 Macro
'


'
    ActiveWindow.SmallScroll ToRight:=-16
    ActiveWindow.SmallScroll Down:=1
    ActiveWindow.SmallScroll ToRight:=-1
    ActiveWindow.SmallScroll Down:=5
    ActiveWindow.SmallScroll ToRight:=-1
    ActiveWindow.SmallScroll Down:=1
    ActiveWindow.SmallScroll ToRight:=1
    ActiveWindow.SmallScroll Down:=-199
    Columns("F:F").Select
    ActiveWorkbook.Worksheets("CLIENT LOG").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("CLIENT LOG").Sort.SortFields.Add Key:=Range( _
        "G2:G11"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortNormal
    With ActiveWorkbook.Worksheets("CLIENT LOG").Sort
        .SetRange Range("A1:G11")
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    Cells.Select
    Range("B1").Activate
    Selection.SUBTOTAL GroupBy:=6, Function:=xlSum, TotalList:=Array(4), _
        Replace:=True, PageBreaks:=False, SummaryBelowData:=True
End Sub

Like I said, it works great if I presort the page but the code was supposed to do that and seems to only sort the first 9 lines of data.....
Thanks in advance for the help.
 

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.
Sort by date Sort by votes
Does this do what you want...

Code: 
Sub SubTotal()


    Dim wsCL As Worksheet: Set wsCL = Worksheets("Client Log")
    Dim lRow As Long
    
    lRow = wsCL.Cells(Rows.Count, 2).End(xlUp).Row
    ActiveWorkbook.Worksheets("CLIENT LOG").Sort.SortFields.Clear
    Range("A1:G" & lRow).Select
    wsCL.Sort.SortFields.Clear
    wsCL.Sort.SortFields.Add Key:=Range("G2:G" & lRow) _
        , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With wsCL.Sort
        .SetRange Range("A1:G" & lRow)
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    wsCL.Range("A1").SubTotal GroupBy:=7, Function:=xlSum, TotalList:=Array(4), _
        Replace:=True, PageBreaks:=False, SummaryBelowData:=True
    Range("A1").Select
    
End Sub
 
Upvote 0
igold said:
does this do what you want...

Code: 
sub subtotal()


    dim wscl as worksheet: Set wscl = worksheets("client log")
    dim lrow as long
    
    lrow = wscl.cells(rows.count, 2).end(xlup).row
    activeworkbook.worksheets("client log").sort.sortfields.clear
    range("a1:g" & lrow).select
    wscl.sort.sortfields.clear
    wscl.sort.sortfields.add key:=range("g2:g" & lrow) _
        , sorton:=xlsortonvalues, order:=xlascending, dataoption:=xlsortnormal
    with wscl.sort
        .setrange range("a1:g" & lrow)
        .header = xlyes
        .matchcase = false
        .orientation = xltoptobottom
        .sortmethod = xlpinyin
        .apply
    end with
    wscl.range("a1").subtotal groupby:=7, function:=xlsum, totallist:=array(4), _
        replace:=true, pagebreaks:=false, summarybelowdata:=true
    range("a1").select
    
end sub
Click to expand...

fabulously!!! Thank you!!
 
Upvote 0
You're welcome. Thanks for the feedback!
 
Upvote 0
You must log in or register to reply here.

Similar threads

J
  • Question Question
Excel macro for Remove comma's and spaces from a cell and place the data in different cell
Replies
3
Views
1K
JEC
JEC
P
  • Question Question
vba to run through sorted list and copy rows in each sorted group into a new tab
Replies
3
Views
1K
vcoolio
V
J
  • Question Question
Copying a row from one sheet to another based on Month and Sorted
Replies
6
Views
1K
sandy666
sandy666
P
  • Question Question
Transfer data in adjacent cells
Replies
3
Views
513
Joe4
Joe4
P
  • Question Question
Clear data if specific columns are blank
Replies
2
Views
588
Panoos64
P

Forum statistics

Threads
1,223,897
Messages
6,175,269
Members
452,628
Latest member
dd2

Share this page

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