Determined if filtered range is empty


F

Fred Smith

I am setting an Autofilter with:

Selection.AutoFilter Field:=3, Criteria1:=Household

However, sometimes Household doesn't exist in the table. How do I test to
see if the selected range is empty? Is there a way to test whether Household
exists in the Criteria1 list? Or is there a test to see if the
Autofilter.Range is empty?
 
Ad

Advertisements

F

Frank Kabel

Hi
try the following
Sub foo()
Dim rng As Range
Set rng = Selection

rng.AutoFilter Field:=3, Criteria1:="Household"
Set rng = ActiveSheet.AutoFilter.Range
If rng.Columns(1).SpecialCells(xlVisible).Count - 1 = 0 Then
MsgBox "no rows to display"
End If
End Sub

This returns a message if now rows are returned by your criteria
 
R

Ron de Bruin

Try this Fred

Sub test()
Selection.AutoFilter Field:=3, Criteria1:=Household
If ActiveSheet.AutoFilter.Range.Cells.SpecialCells(xlCellTypeVisible).Count _
= Selection.Columns.Count Then MsgBox "no cells"
End Sub
 
F

Fred Smith

Thanks for both responses. It works great.

Is there any difference between xlCellTypeVisible and xlVisible? I can't
find xlVisible in Help, but it seems to work just as well, and it's a lot
easier to type.

--
Regards,
Fred
Please reply to newsgroup, not e-mail


Ron de Bruin said:
Try this Fred

Sub test()
Selection.AutoFilter Field:=3, Criteria1:=Household
If
ActiveSheet.AutoFilter.Range.Cells.SpecialCells(xlCellTypeVisible).Count _
 
T

Tom Ogilvy

There is no difference.


? xlcelltypevisible
12
? xlvisible
12
 
Ad

Advertisements

D

Dave Peterson

The both represent the number 12 in xl2002.

From the immediate window:

?xlCellTypeVisible
12
?xlvisible
12

I think that xlcelltypevisible was added in later versions (xl97????). My guess
is that it is more self-documenting--but that's a guess.

Next time you're in the VBE, hit F2 to see the Object Browser.

Search for both and you'll see them.
 
Ad

Advertisements


Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top