处理“未找到细胞".过滤范围为空时发生错误 [英] 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

错误1004未找到单元格

Error 1004 No cells were found

我正在寻找一种方法(无On Error)来检查过滤范围是否为空.

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

我已经尝试使用lastRow = .Cells(.Rows.Count, ColumnName).End(xlUp).Row设置范围并检查if lastRow > 0，但是通过这种方式，我还可以计算过滤后的(或隐藏的)行内容.

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.

我也尝试过

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

但是在这里，我在Set rngFiltered行中也收到错误找不到单元格".

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

我没有标题行，因为过滤器是如此复杂，以至于我不使用.Sort函数就对其进行了编程

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

推荐答案

我将解决方案存储到一个函数中.在这里，我对机械强度使用了一个错误.

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

这篇关于处理“未找到细胞".过滤范围为空时发生错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！