Excel – VBA to sort table and ignore total row

excelexcel-2010vba

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.

Best Answer

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
Related Topic