CODEWITHSUNDEEP
excelvba
ruedi
ruedi

Reputation: 5545

Handle "No cells were found" error when filtered range is empty

I do some filtering on a range and copy that filtered range with

myRange.SpecialCells(xlCellTypeVisible).Copy

As soon as the filter filters all cases I get

Error 1004 No cells were found

I am looking for a way to check (without an On Error) if the filtered range is empty.

I already tried to set a range with lastRow = .Cells(.Rows.Count, ColumnName).End(xlUp).Row and check if lastRow > 0 but with this way I also count the filtered (or hidden) row contents.

I also tried

Sub test()
    Dim rngStart As Range
    Dim rngFiltered As Range

    Set rngStart = Sheets(1).Range("A1:A6")
    Set rngFiltered = rngStart.SpecialCells(xlCellTypeVisible).Select

    If rngFiltered.Rows.Count = 0 Then
        MsgBox ("No Cases")
    Else
        MsgBox ("Found Cases")
    End If
End Sub

But here I get the error "No cells found" in the Set rngFiltered line as well.

I have no header row, since the filter is so complex that I programmed it without using the .Sort function

Upvotes: 6

Views: 43292

Answers (4)

Tim Williams
Tim Williams

Reputation: 166126

Dim rngStart As Range
Dim rngFiltered As Range

'...
'...
Set rngFiltered = Nothing '<<< reset rngFiltered if running this code in a loop...
On Error Resume Next
Set rngFiltered = rngStart.SpecialCells(xlCellTypeVisible)
On Error Goto 0

If not rngFiltered is Nothing then
    rngFiltered.Copy
End If
'...
'...

Upvotes: 15

Tomek S
Tomek S

Reputation: 27

Got the same problem, but for filtering named table, solved it this way*:

  1. instead of applying several filters, I've added a column at the end of the table, with a formula that would return true for the rows I wanted to have filtered in, false for filtered out.
  2. Then, I've applied one filter for that true value and added a cell that would count all true values in that column
  3. in vba, firstly reapply filter for the table then if that counter is greater than 0 do .SpecialCells(xlCellTypeVisible).Copy, else skip to next step (i was doing that in a loop)

*I know that this question is from 2015, but I've ended here in 2019 googling similar problem so I'm leaving my solution.

Upvotes: 0

ruedi
ruedi

Reputation: 5545

I stored the solution into a function. Here I use an error on mechamism. 

Function errorCatchEmptyFilter(ByRef rngstart As Range) As Boolean

errorCatchEmptyFilter = False

'here I get an error if there are no cells
    On Error GoTo hell
    Set rngFiltered = rngstart.SpecialCells(xlCellTypeVisible)

Exit function

hell:
errorCatchEmptyFilter = True

End Function

Upvotes: 7

mauek unak
mauek unak

Reputation: 772

What I do is I am counting filtered rows :

Sheets("Sheet1").Range("A2:Z2").AutoFilter
Sheets("Sheet1").Range("A2:Z2").AutoFilter Field:=1, Criteria1:=filter1
If Sheets("Sheet1").AutoFilter.Range.Columns(4).SpecialCells(xlCellTypeVisible).Count > 1 Then

you can change number of column to suits your needs

Upvotes: 2

Related Questions