VBA中的自动筛选不返回数据时该怎么办? [英] What to do when autofilter in VBA returns no data?

问题描述

我正在尝试过滤一系列值，并根据我的标准，有时我可能没有符合我标准的数据.在那种情况下，我不想从过滤的数据中复制任何数据.如果有已过滤的数据，那么我想将其复制.

I am trying to filter a range of values and based on my criteria, at times I might have no data that fits my criteria. In that case, I do not want to copy any data from the filtered data. If there is filtered data, then I would like to copy it.

这是我的代码:

With Workbooks(KGRReport).Worksheets(spreadSheetName).Range("A1:I" & lastrowinSpreadSheet)
    .AutoFilter Field:=3, Criteria1:=LimitCriteria, Operator:=xlFilterValues 'Do the filtering for Limit
     .AutoFilter Field:=9, Criteria1:=UtilizationCriteria, Operator:=xlFilterValues 'Do the filtering for Bank/NonBank
End With

'Clear the template
 Workbooks(mainwb).Worksheets("Template").Activate
 Workbooks(mainwb).Worksheets("Template").Rows(7 & ":" & Rows.Count).Delete

 'Copy the filtered data
 Workbooks(KGRReport).Activate
 Set myRange = Workbooks(KGRReport).Worksheets(spreadSheetName).Range("B2:H" & lastrowinSpreadSheet).SpecialCells(xlVisible)
 For Each myArea In myRange.Areas
     For Each rw In myArea.Rows
           strFltrdRng = strFltrdRng & rw.Address & ","
     Next
 Next

 strFltrdRng = Left(strFltrdRng, Len(strFltrdRng) - 1)
 Set myFltrdRange = Range(strFltrdRng)
 myFltrdRange.Copy
 strFltrdRng = ""

这给我一个错误

Set myRange = Workbooks(KGRReport).Worksheets(spreadSheetName).Range("B2:H" & lastrowinSpreadSheet).SpecialCells(xlVisible)

当完全没有数据时，它会返回错误:未找到任何单元格".

When there is no data at all, it is returning an error: "No cells found".

尝试过这样的错误处理: 1004错误:没有单元格找到了，简单的解决方法?

Tried error handling like this post: 1004 Error: No cells were found, easy solution?

但这没有帮助.需要一些有关如何解决此问题的指导.

But it was not helping. Need some guidance on how to solve this.

推荐答案

尝试像这样处理错误:

Dim myRange As Range

On Error Resume Next
Set myRange = Range("your range here").SpecialCells(xlVisible)
On Error GoTo 0

If myRange Is Nothing Then
    MsgBox "no cells"
Else
    'do stuff
End If

这篇关于VBA中的自动筛选不返回数据时该怎么办?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！