Excel VBA - 检查过滤表是否返回任何结果
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/16087753/
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
Excel VBA - Check whether a filtered table returns any results
提问by AdamDynamic
I have a macro that filters a table (in the code as a ListObject) and then copies the visible cells in the DataBodyRange to a seperate table. The code works fine unless the filtering action removes all the data (i.e. the table only has the header row and nothing else).
我有一个宏可以过滤一个表（在代码中作为 ListObject），然后将 DataBodyRange 中的可见单元格复制到一个单独的表中。除非过滤操作删除所有数据（即表只有标题行，没有其他内容），否则代码工作正常。
Is there a neat way to check whether any rows are visible? I'd like to avoid on error resumeterms if possible, I'm struggling to think of any other way though?
有没有一种简洁的方法来检查是否有任何行可见？on error resume如果可能的话，我想避免使用条款，但我正在努力想其他方式吗？
I've included some pseudocode below to illustrate what I mean, any assistance would be much appreciated!
我在下面包含了一些伪代码来说明我的意思，任何帮助将不胜感激！
Adam
亚当
If TargetTable.DataBodyRange.VisibleRows.Count > 0 Then
TargetTable.DataBodyRange.SpecialCells(xlCellTypeVisible).Copy Destination:=OutputPasteRange
End If
回答by David Zemens
Use the Table's Rangeobject, not the DataBodyRange. Then, check to make sure that .SpecialCells(xlCellTypeVisible).Rows.Count > 1.
使用 Table 的Range对象，而不是DataBodyRange. 然后，检查以确保.SpecialCells(xlCellTypeVisible).Rows.Count > 1.
Sub TestEmptyTable()
Dim tbl As ListObject
Dim outputPasteRange As Range
Dim tblIsVisible As Boolean
Set tbl = ActiveSheet.ListObjects(1)
Set outputPasteRange = Range("B15")
If tbl.Range.SpecialCells(xlCellTypeVisible).Areas.Count > 1 Then
tblIsVisible = True
Else:
tblIsVisible = tbl.Range.SpecialCells(xlCellTypeVisible).Rows.Count > 1
End If
If tblIsVisible Then
tbl.DataBodyRange.SpecialCells(xlCellTypeVisible).Copy _
Destination:=outputPasteRange
Else:
MsgBox tbl.Name & " has been filtered to no visible records", vbInformation
End If
End Sub
回答by Slai
Just check if the Range.Heightis not 0:
只需检查是否Range.Height为 0：
If [Table1].Height Then
Also, .SpecialCells(xlCellTypeVisible)is not needed when the .Heightis more than 0:
此外，.SpecialCells(xlCellTypeVisible)当.Height大于 0时不需要：
If TargetTable.DataBodyRange.Height Then TargetTable.DataBodyRange.Copy OutputPasteRange
回答by LazerEyes01
An alternate approach would be to compare .SpecialCells(xlCellTypeVisible).Addressto the header row address, tbl.HeaderRowRange.Address.
另一种方法是.SpecialCells(xlCellTypeVisible).Address与标题行地址进行比较tbl.HeaderRowRange.Address。
Here is a variation of David's code:
这是大卫代码的变体：
Sub TestEmptyTable()
Dim tbl As ListObject
Dim outputPasteRange As Range
Dim tblIsVisible As Boolean
Set tbl = ActiveSheet.ListObjects(1)
Set outputPasteRange = Range("B15")
tblIsVisible = tbl.Range.SpecialCells(xlCellTypeVisible).Address <> _
tbl.HeaderRowRange.Address
If tblIsVisible Then
tbl.DataBodyRange.SpecialCells(xlCellTypeVisible).Copy _
Destination:=outputPasteRange
Else
MsgBox tbl.Name & " has been filtered to no visible records", vbInformation
End If
End Sub
