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

[rmarenyi]

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.

LOT	Description	QTY	Client Share	PAID	DATE PAID	CHECK #	<img width="136" height="42" alt="Create Client Summary"> <tbody> </tbody>
x	mirror ornate		40.00	y	6/19/18	1169
x	gold mirror oval		15.00	y	4/30/18	1129
x	round table gold grim		50.00	y	6/19/18	1169
x	stork		62.50	y	6/19/18	1169
x	gold mirrored candle holders	2	50.00	y	4/30/18	1129
x	caned vanity chair		25.00	y	6/19/18	1169
x	demilune table		100.00	y	4/30/18	1129
x	NM mirrors	3	55.00	y	6/19/18	1169
x	drop down small desk		62.50	y	4/30/18	1129
x	RECORD PLAYER		50.00	y	4/30/18	1129
x	side table w/glass		25.00	y	6/19/18	1169
x	sunflower oil painting		50.00	y	6/19/18	1169
x	KJV bible		37.50	y	6/19/18	1169
x	TK Beside Still Waters		32.50	y	6/19/18	1169
x	TK Light of Peace		75.00	y	4/30/18	1129
x	calligraphy michael podesta		20.60	y	6/19/18	1169
x	Sunflowers on canvas		30.00	y	4/30/18	1129
x	oil paintings whimsical instruments	2	100.00	y	4/30/18	1129
x	coral silk french chairs	2	225.00	y	6/19/18	1169
x	ice cream chairs	2	150.00	y	6/19/18	1169
x	red velvet vanity chair		40.00	y	6/19/18	1169
x	gold chair pink seat		17.50	y	7/20/18	1186
x	gold marble pedestal table		52.92	y	7/20/18	1186
	cherry side table		60.00	y	6/19/18	1169
x	velvet stools	2	25.00	y	7/20/18	1186
x	side table tile top		50.00	y	6/19/18	1169
x	child rolltop desk		32.50	y	7/20/18	1186

<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>
</tbody>

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

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.

 

[igold]

Does this do what you want...

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

 

[rmarenyi]

does this do what you want...

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

fabulously!!! Thank you!!

 

[igold]

You're welcome. Thanks for the feedback!