vba VBA对表进行排序并忽略总行
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/20586429/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
VBA to sort table and ignore total row
提问by yinka
I have the range Table3as shown below:
我有Table3如下所示的范围：
The rows are not fixed and could increase or decrease, I have thus created it as a table Table3to accommodate this behavior and also so I could use it in a VBA as a ListObjects.
行不是固定的，可以增加或减少，因此我将它创建为一个表Table3来适应这种行为，而且我可以在 VBA 中将它用作ListObjects.
The VBA below is meant to sort the table, however because the Totalsis part of the range, the sort doesn't work as intended.
下面的 VBA 旨在对表格进行排序，但是由于它Totals是范围的一部分，因此排序无法按预期工作。
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 Totalsrow (i.e to include only the range below the headerand above the Totalsrow) before applying the sort
在应用排序之前，有人可以帮助修改代码以忽略该Totals行（即仅包括header该Totals行下方和上方的范围）
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.
任何帮助将不胜感激。
回答by nixda
Set a new range for your table, just one row shorter ? totalRowCount - 1.
Here, xis your input range
为您的表格设置一个新范围，仅短一行？totalRowCount - 1.
这x是你的输入范围
Set x = Range(x.Cells(1, 1), x.Cells(x.Rows.Count - 1, x.Columns.Count))
or use the resizemethod
或使用调整大小方法
Sub CutOffLastLine()
With ActiveWorkbook.Worksheets("Project 2013").ListObjects("Table3")
.Resize .Range.Resize(.Range.Rows.Count - 1, .Range.Columns.Count)
End With
End Sub
