Catching No Records When Using Filter In Place

Just as when using Copy, you have to watch out for the possibility of having no records match the criteria. In this case, however, it is more difficult to realize that nothing is returned. You generally find out when the SpecialCells method returns a Run-time error 1004—no cells were found.

To catch this condition, you have to set up an error trap to anticipate the 1004 error with the .SpecialCells method. (See Chapter 23, "Handling Errors," for more information on catching errors.)

On Error GoTo NoRecs

For Each cell In Range("A2:A" & FinalRow).SpecialCells(xlCellTypeVisible)

MsgBox Ctr & " cells match the criteria" Exit Sub NoRecs:

MsgBox "No records match the criteria" End Sub

This error trap works because I specifically exclude the header row from the SpecialCells range. The header row is always visible after an advanced filter. Including it in the range and would prevent the 1004 error from being raised.

0 0

Post a comment