Macro - message when there is nothing to filter

M

misskh

Hi,

Please help.

For a certian column, I currently I have a macro that filters when
cells = "WWW".

How do I include in this macro a message for the user when there is
nothing to filter.

Thank you.
 
C

Chip Pearson

I'm not sure what you're looking for, but the following may point you
in the right direction. If your data is in A1:A100 where A1 is the
heading, use code like

Sub AAAA()
Dim R As Range
Set R = Range("A2:A100")
With Application.WorksheetFunction
If .CountA(R) = 0 Then
Debug.Print "no data to filter"
Else
If .Subtotal(103, R) = .CountA(R) Then
Debug.Print "no items filtered"
Else
Debug.Print "some items filtered"
End If
End If
End With
End Sub

This tests whether the data list is empty (no data to filter), or some
values have been filtered out, or no items have been filtered out.

Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)
 
Top