Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 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
|
|||
|
|||
Quote:
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Need help (Average excluding zero values) | Excel Discussion (Misc queries) | |||
How calculate a MIN excluding the 0 values | Excel Discussion (Misc queries) | |||
Excluding Error Values | Charts and Charting in Excel | |||
Min Function Excluding Zero Values & More | Excel Worksheet Functions | |||
excluding repeating values | Excel Discussion (Misc queries) |