If filtered range is blank, message box

[Afro_Cookie]

I filter my data based on entries in field 8 and once I have that filtered I want a message box to pop up to let me know if there are any new entries.

The filtering works, and I know that the message box criteria is wrong since cell H2 won't be blank. It's just not in the filtered range, but I'm not sure how to format my message box correctly to only prompt if no values were filtered. Can someone please help me with this?

Sub mmsn()

ActiveSheet.ListObjects("Table1").Range.AutoFilter Field:=8, _
    Criteria1:=Array("36011771", "36011857", "36000007", "36001281", "36000695"), Operator:=xlFilterValues

If ActiveSheet.Range("$H2").Value = "" Then
    MsgBox "There are no new entries", vbOKCancel
    Exit Sub
End If

End Sub

Book1
	A	B	C	D	E	F	G	H	I
1	a	b	c	d	e	f	g	h	i
2	1	2	3	4	5	6	7	8	9
3	1	2	3	4	5	6	7	8	9
4	1	2	3	4	5	6	7	8	9
Sheet1

 

[Fluff]

Do you want the message box if all the data is hidden?

 

[Afro_Cookie]

Do you want the message box if all the data is hidden?

in an ideal case, if all the data is hidden, it means there are no new entries in field 8 which would then trigger the message box.

 

[Fluff]

Ok, how about

With ActiveSheet.ListObjects("Table1").Range
   .AutoFilter Field:=8, _
    Criteria1:=Array("36011771", "36011857", "36000007", "36001281", "36000695"), Operator:=xlFilterValues
   If .Columns(1).SpecialCells(xlVisible).Count = 1 Then MsgBox "There are no new entries"
End With

 

[Afro_Cookie]

Ok, how about

With ActiveSheet.ListObjects("Table1").Range
   .AutoFilter Field:=8, _
    Criteria1:=Array("36011771", "36011857", "36000007", "36001281", "36000695"), Operator:=xlFilterValues
   If .Columns(1).SpecialCells(xlVisible).Count = 1 Then MsgBox "There are no new entries"
End With

As always Fluff, this works and is very helpful. Thank you very much!

 

[Fluff]

You're welcome & thanks for the feedback.