VBA:自动筛选器不返回任何数据时的输出消息框 [英] VBA: Output message box when Autofilter returns no data
问题描述
如果过滤后有任何结果,我想将自动过滤的范围复制并粘贴到新的工作表中,如果没有结果,则显示一个消息框.
I want to copy and paste auto-filtered range to a new worksheet if there are any results after filtering, and show a message box if there is no result.
但是,当我使用不会返回任何结果的过滤条件进行测试时,不会出现消息框(显示空白工作表)
However, when I test using a filter criteria that would not return any results, the message box does not appear (blank worksheet shows)
Dim WSNew As Worksheet
Set WSNew = Worksheets.Add
Dim rngVisible As Range
Set rngVisible = ActiveSheet.AutoFilter.Range.SpecialCells(xlCellTypeVisible)
If rngVisible.Rows.Count > 1 Or rngVisible.Areas.Count > 1 Then
rngVisible.Copy
With WSNew.Range("A1")
.PasteSpecial Paste:=8
.PasteSpecial xlPasteValues
.PasteSpecial xlPasteFormats
Application.CutCopyMode = False
.Select
End With
Else
MsgBox ("No such filtered criteria")
End If
推荐答案
首先,您想在活动工作表中工作,但是在执行工作表时.添加的工作表可能会成为活动工作表(取决于我认为的Excel版本).那可能是个问题.因此,您必须设置一个WSOld并对其进行处理.
First you want to work in the active sheet but when you execute Worksheets.Add the worksheet added can become the active sheet (depends on Excel versions I think). That can be an issue. So you have to set a WSOld and work on it.
此外,您的自动筛选器功能的顺序不正确(首先声明Worksheet.Range(firstColumfirstLine:lastColumLastLine),然后对其自动筛选:
Moreover, your autofilter function is not in the right order (first declare the Worksheet.Range(firstColumfirstLine : lastColumLastLine) and then autofilter on it : https://msdn.microsoft.com/fr-fr/library/office/ff193884.aspx).
您还必须选择标准来过滤数据.
You have also to choose the criteria(s) to filter the data.
然后使用UsedRange.SpecialCells(xlCellTypeVisible)设置一个包含过滤单元格的范围并在其上进行交互.
And then use the UsedRange.SpecialCells(xlCellTypeVisible) to set a range with the filtering cells and interact on it.
这对我有用:
Dim WSOld As Worksheet
Dim WSNew As Worksheet
'store the active sheet in WSOld to be sure that selection will be apply on it
Set WSOld = ActiveSheet
Set WSNew = Worksheets.Add
'select the range to apply the filter and choose criteria
WSOld.Range("A1:B6500").AutoFilter Field:=2, Criteria1:="te"
'select the data visible after filter
Dim rngVisible As Range
Set rngVisible = WSOld.UsedRange.SpecialCells(xlCellTypeVisible)
If rngVisible.Rows.Count > 1 Or rngVisible.Areas.Count > 1 Then
rngVisible.Copy
With WSNew
.Range("A1").PasteSpecial Paste:=8
.Range("A1").PasteSpecial xlPasteValues
.Range("A1").PasteSpecial xlPasteFormats
Application.CutCopyMode = False
.Select
End With
Else
MsgBox ("No such filtered criteria")
End If
'remove autofilter
WSOld.Range("A1:B6500").AutoFilter
希望有帮助.
这篇关于VBA:自动筛选器不返回任何数据时的输出消息框的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!