Filtering/Excluding Values

[KeriM]

I'm trying to filter values on two columns, then count the number o
rows remaining with the result displayed in a pop-up message box, an
then exclude values on two columns. So basically, I'm filtering fou
columns total, but breaking them up into two sections so that I ca
count the number of rows after the first set of filtering.

This is what I have so far:


Code
-------------------


Sub FilterCol()
Cells.Select
Cells.EntireColumn.AutoFit
Selection.AutoFilter
Dim rng As Range
Dim res As Variant
Set rng = ActiveSheet.AutoFilter.Range.Rows(1)
res = Application.Match("Measure1", rng, 0)
res1 = Application.Match("Flag", rng, 0)
If Not IsError(res) Then
rng.AutoFilter Field:=res, Criteria1:="< 100"
Else
MsgBox "Filter category was not found"
End If
If Not IsError(res2) Then
rng.AutoFilter Field:=res1, Criteria1:="FALSE"
Else
MsgBox "Filter category was not found"
End If
End Sub

Sub CountCells2()
Dim UpperLeftCorner As Range
' UpperLeftCorner should be set to the upper-left
' corner of the list range:
Set UpperLeftCorner = ActiveSheet.Range("A2")
RowCount = -1
For Each area In _
UpperLeftCorner.CurrentRegion.SpecialCells(xlVisible).Areas
RowCount = RowCount + area.Rows.Count
Next
MsgBox "The Row Count is " & RowCount
End Sub

Sub FilterCol2()
Cells.Select
Selection.AutoFilter
Dim rng1 As Range
Dim res As Variant
Set rng1 = ActiveSheet.AutoFilter.Range.Rows(1)
res2 = Application.Match("Value 1", rng1, 0)
res3 = Application.Match("Date", rng1, 0)
If Not IsError(res2) Then
rng1.AutoFilter Field:=res2, Criteria1:="<>.0000000000*"
Else
MsgBox "Filter category was not found"
End If
If Not IsError(res3) Then
rng1.AutoFilter Field:=res3, Criteria1:="<>1900-01-01 00:00:00*"
Else
MsgBox "Filter category was not found"
End If
End Sub


-------------------


The problem I'm running into is that I'm getting an error message on m
second set of code. "Run-time error '91': Object variable or With bloc
variable not set"

It's highlighting this line: "Set rng1
ActiveSheet.AutoFilter.Range.Rows(1)"

I don't understand since it's exactly the same as the first set of cod
except that the headers have changed (and the exclusion).

If someone can help me out, I'd really appreciate it. Also, if you ca
think of a way to simplify my code, I'm open to suggestions

 

[pascal baro]

Hi,

Below is an example of a range object:
---
Set r = ws.Range("A1").CurrentRegion
With r
.AutoFilter 13, "NZzzM", xlAnd
Set rv = .Offset(1).SpecialCells(xlCellTypeVisible)
End With