Autofilter:How to determine if the func returned no matches

[jminu]

Hi

I'm using the Autofilter option and I'm filtering for criteri
"NonBlanks".
If there are no "NonBlanks" cells in that coln I have to move to nex
coln
else a paticular procX has to be executed.

My difficulty is how to determine if Autofilter for criteri
"NonBlanks" returned nothing. ie all cells in the choosen coln ar
blank


Thx,
Min

 

[Art Farrell]

Hi Minu,

Assuming you mean through a macro try the following and substitute your
process call where I have a MsgBox:

Option Explicit
Sub blankCols()
Dim rng As Range, visRows As Long
Dim numFields As Long, fieldCnt As Long

numFields = Application.CountA(Rows("1:1"))
Set rng = Range("A1").CurrentRegion
fieldCnt = 1
Do
rng.AutoFilter Field:=fieldCnt, Criteria1:="<>"
Set rng = ActiveSheet.AutoFilter.Range
visRows = Application.Subtotal(3, rng.Columns(fieldCnt))
If visRows <> 1 Then
MsgBox "Call process"
End If
Selection.AutoFilter
fieldCnt = fieldCnt + 1
Loop Until fieldCnt = numFields + 1
End Sub

CHORDially,
Art Farrell