View Single Post
  #1   Report Post  
KeriM KeriM is offline
Member
 
Posts: 70
Default 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:

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 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.