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
– Pieter Geerkens, Commented Dec 14, 2013 at 18:28
I'd try using an actual Excel table, which has built-in totals. Then record a macro that does the sort. — Doug Glancy
– Doug Glancy, Commented Dec 14, 2013 at 19:03
@DougGlancy Thanks, but for a number of reasons that wont work — yinka
– yinka, Commented Dec 14, 2013 at 19:19
@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
– yinka, Commented Dec 14, 2013 at 20:49

nixda · Accepted Answer · 2013-12-14 21:23:41Z

1

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

edited Dec 14, 2013 at 21:23

answered Dec 14, 2013 at 21:15

nixda

2,76713 gold badges54 silver badges88 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

yinka Over a year ago

Dim resizedTable As ListObject Set resizedTable3 = ActiveWorkbook.Worksheets("Project 2013").ListObjects("Table1") With ResizedTable .Resize .Range.Resize(.Range.Rows.Count - 1, .Range.Columns.Count) I tried the above similar to your suggestion but I think I have inserted it in the wrong order. I'll try again

nixda Over a year ago

I guess you found the resize method at the same time :)

Collectives™ on Stack Overflow

VBA to sort table and ignore total row

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related