Filtering/Excluding Values

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

This is what I have so far:

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 my second set of code. "Run-time error '91': Object variable or With block 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 code except that the headers have changed (and the exclusion).

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

Originally Posted by KeriM

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

This is what I have so far:

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 my second set of code. "Run-time error '91': Object variable or With block 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 code except that the headers have changed (and the exclusion).

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

Not sure how to delete a thread, but I've solved my own problem. When I was running the code back-to-back while testing it, I didn't reset the autofilter, so it was unfiltering the columns the 2nd time I ran it, and caused the error.

Putting this before my filter code did the trick to reset:

Sub AutoFilterOff()
    With ActiveSheet
    .AutoFilterMode = False
    End With
End Sub

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
---
By using 2 objects, it makes object range autofilter methods easier.

Pascal Baro