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

5
votes

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

4
excelvba
If you're filtering there should always be one row visible - the header row. You can use that to test if there's more than one row visible - then resize to 1 row fewer and offset by 1 before copying. – Rory
Hi! Unfortunately I have no header row, since the filter is so complex that I programmed it without using the .Sort function. – ruedi
Then it's not a filter, it's a load of manually hidden rows. Filters always require a header row. In that case your code should be able to determine how many rows it left unhidden. What's the problem with On Error? – Rory
How can I determine how many rows are left unhidden? If rngStart.Rows.SpecialCells(xlCellTypeVisible).Count = 0 still gives me the error mentioned above. I tried to avoide the On Error since I do not want to jump around in the code and just need a value to check. Bust I just wrote a function that does this for me with an error on. – ruedi
Use On Error Resume Next, assign the visible cells to a Range variable, then On Error Goto 0 and then test if the range is Nothing. Since you haven't posted the code that hid the rows, it's hard to suggest another way. – Rory

4 Answers

10
votes
On Error Resume Next
Set rngFiltered = rngStart.SpecialCells(xlCellTypeVisible)
On Error Goto 0

If not rngFiltered is Nothing then
    rngFiltered.Copy
End If
7
votes

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
1
votes

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

0
votes

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.