VBA中的自动筛选不返回数据时该怎么办? [英] What to do when autofilter in VBA returns no data?
本文介绍了VBA中的自动筛选不返回数据时该怎么办?的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我正在尝试过滤一系列值,并根据我的标准,有时我可能没有符合我标准的数据.在那种情况下,我不想从过滤的数据中复制任何数据.如果有已过滤的数据,那么我想将其复制.
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屋!
查看全文