Error handling an empty filtered range.

[Gringoire]

Hello everybody,

I have to obtain a sum from some filtered cells (see code below). Unfortunately the code below raises a 1004 error when the result of filtering operation is an empty range.
I would like to avoid using "On error..." procedures because it could mask some other unexpected error.
I would also like to avoid to use sumifs() instead of filters because I need to put multiple conditions on some columns and that needs a big amount of workaround code...

is there a way to verify the filtered range without raising an error if it is empty?
Statements like :

If Not (.SpecialCells(xlCellTypeVisible)) Is Nothing

or

If IsError (.SpecialCells(xlCellTypeVisible))

seems not working...

My code:

Function filteredsum()
    Dim risultati(1 To 2)
    With shRawData.ListObjects("TabRawData").DataBodyRange
        If Not (.SpecialCells(xlCellTypeVisible)) Is Nothing Then
            risultati(1) = WorksheetFunction.Sum(.Columns(RWC.valtot).SpecialCells(xlCellTypeVisible))
            risultati(2) = WorksheetFunction.Sum(.Columns(RWC.qta).SpecialCells(xlCellTypeVisible))
        Else
            risultati(1) = 0
            risultati(2) = 0
        End If
    End With
    filteredsum = risultati
End Function

thanks.

 

[Gringoire]

I solved this issue thanks to this old post:

Check if range is empty (Visible Cells)

Hi I have am using an auto filter and looping through the visible cells that remain. I get an error when there is no data visible based on the filtered criteria. I have the following code... With Sheets("DB") Set TableRange = .Range(.Cells(Origin.Row + 3, Origin.Column + x), .Cells(End.Row...

www.mrexcel.com

modified code:

Function filteredsum()
Dim risultati(1 To 2)
With shRawData.ListObjects("TabRawData").DataBodyRange
If Not .EntireRow.Hidden Then Then
risultati(1) = WorksheetFunction.Sum(.Columns(RWC.valtot).SpecialCells(xlCellTypeVisible))
risultati(2) = WorksheetFunction.Sum(.Columns(RWC.qta).SpecialCells(xlCellTypeVisible))
Else
risultati(1) = 0
risultati(2) = 0
End If
End With
filteredsum = risultati
End Function

 

[Domenic]

It looks like the only time If Not .EntireRow.Hidden Then will evaluate to True is when the data is not filtered.

Also, On Error Resume Next will mask other unexpected errors only when used improperly.

Try the following...

Function filteredsum()
    Dim filteredRange As Range
    Dim risultati(1 To 2)
    With shRawData.ListObjects("TabRawData").DataBodyRange
        On Error Resume Next
        Set filteredRange = .SpecialCells(xlCellTypeVisible)
        On Error GoTo 0
        If Not filteredRange Is Nothing Then
            risultati(1) = WorksheetFunction.Sum(.Columns(RWC.valtot).SpecialCells(xlCellTypeVisible))
            risultati(2) = WorksheetFunction.Sum(.Columns(RWC.qta).SpecialCells(xlCellTypeVisible))
        Else
            risultati(1) = 0
            risultati(2) = 0
        End If
    End With
    filteredsum = risultati
End Function

Hope this helps!

 

[Gringoire]

You are right about both points:
- .EntireRow.Hidden does not work properly
- on error statement if used properly, as you did, do not cause other issues.

thanks a lot!

 

[Domenic]

You're very welcome, and thanks for the feedback.

Cheers!