VBA to sort table and ignore total row

Question

I have the range Table3 as shown below:

enter image description here

The rows are not fixed and could increase or decrease, I have thus created it as a table Table3 to accommodate this behavior and also so I could use it in a VBA as a ListObjects.

The VBA below is meant to sort the table, however because the Totals is part of the range, the sort doesn't work as intended.


Sub sort()

    ActiveWorkbook.Worksheets("Project 2013").ListObjects("Table3").sort.SortFields _
        .Clear
    ActiveWorkbook.Worksheets("Project 2013").ListObjects("Table3").sort.SortFields _
        .Add Key:=Range("Table3[Description3]"), SortOn:=xlSortOnValues, Order:= _
        xlAscending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("Project 2013").ListObjects("Table3").sort
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
End Sub

Can someone please help modify the code to ignore the Totals row (i.e to include only the range below the header and above the Totals row) before applying the sort

EDIT

At the moment, this is my attempt at redefining a new range without the last row

Sub sort()

Dim resizedTable As ListObject
Set resizedTable = Sheets("Sheet1").ListObjects("Table1")
With resizedTable
.Resize .Range.Resize(.Range.Rows.Count - 1, .Range.Columns.Count)
End With


    resizedTable.sort.SortFields.Clear
    resizedTable.sort.SortFields _
        .Add Key:=Range("resizedTable[Description]"), SortOn:=xlSortOnValues, Order:= _
        xlAscending, DataOption:=xlSortNormal
   
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply

End Sub

Any help will be appreciated.

Just make the range one row shorter, and then sort. Use the Range method of the Range object. — Pieter Geerkens
I'd try using an actual Excel table, which has built-in totals. Then record a macro that does the sort. — Doug Glancy
@DougGlancy Thanks, but for a number of reasons that wont work — yinka
@DougGlancy - I defined a table range in excel but another application inserts the table data and the (sub)totals totals, I unchecked the Excel-Table options that inserts subtotals because of this - as far as Excel is concerned, the table has no (sub)total. — yinka

nixda nixda · Accepted Answer · 2013-12-14T21:15:05

Set a new range for your table, just one row shorter » totalRowCount - 1.
Here, x is your input range

Set x = Range(x.Cells(1, 1), x.Cells(x.Rows.Count - 1, x.Columns.Count))

or use the resize method

Sub CutOffLastLine()
    With ActiveWorkbook.Worksheets("Project 2013").ListObjects("Table3")
        .Resize .Range.Resize(.Range.Rows.Count - 1, .Range.Columns.Count)
    End With
End Sub

VBA to sort table and ignore total row

1 Answers