Using autofilter results

[Cleber Inacio]

Hi,

I have a sheet with a reader at the top e with lots of lines of data bellow
it.

I need to fill a column with a flag(1,2 ,3 or 4) , depending on the data
contained in each row.
I'm using autofilter to select some of the rows e filll that column(called
TYPE).

Above some piece of code I'm using:

Set rnData = .UsedRange
With rnData
Selection.AutoFilter Field:=33, Criteria1:=">0", Operator:=xlAnd
a = Selection.SpecialCells(xlCellTypeVisible).Areas(2).Rows.Count
If Not (Selection.SpecialCells(xlCellTypeVisible).Rows.Count = 1 _
And Selection.SpecialCells(xlCellTypeVisible).Areas.Count = 2)
Then
.Offset(1, 0).Resize(.Rows.Count - 1, .Columns.Count) _
.SpecialCells(xlCellTypeVisible).Select
...

A problem that raised during my tests was to check if the filtering i
just done
returned no results, because in that situation i didnt need to fill de TYPE
column.
After some tests i figured out that no results was equal to
Rouws.COunt = 1 and Areas.Count = 2, as u may see in the above code.
I'm not sure if this trick will work always, so I ask you:
Is there any better way to check a no results filtering??

Cleber

 

[sebastienm]

Hi
Instead of using UsedRange and Selection, I would use the range of the
filter: AutoFilter.Range .
Then you can compare the number of visible cells to the number of cells in
the header.

Dim rg as range
''' Get the data range on which the filter applies (assumes FIlter is ON)
set rg=ActiveSheet.AutoFilter.Range
''' # cells in header vs # cells in filtered range
If rg.Rows(1).cells.count = rg.SpecialCells(xlCellTypeVisible).Count Then
''' NO RESULT
End if