What to do when autofilter in VBA returns no data?

Question

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.

Here is my code:

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 = ""

It is giving me an error at

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".

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.

Answer 1

Try error handling like so:

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