Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
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.
  #2   Report Post  
Member
 
Posts: 70
Default

Quote:
Originally Posted by KeriM View Post
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.

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:

Code:
Sub AutoFilterOff()
    With ActiveSheet
    .AutoFilterMode = False
    End With
End Sub
  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 23
Default Filtering/Excluding Values

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
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Need help (Average excluding zero values) Mike Excel Discussion (Misc queries) 2 January 9th 10 10:10 PM
How calculate a MIN excluding the 0 values jimmy Excel Discussion (Misc queries) 5 September 10th 08 03:10 PM
Excluding Error Values Rod Behr Charts and Charting in Excel 1 May 24th 07 01:25 PM
Min Function Excluding Zero Values & More WeatherGuy Excel Worksheet Functions 8 January 1st 06 05:44 AM
excluding repeating values neda5 Excel Discussion (Misc queries) 2 July 10th 05 11:59 PM


All times are GMT +1. The time now is 08:02 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"