Home |
Search |
Today's Posts |
#1
![]() |
|||
|
|||
![]()
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. |
Thread Tools | Search this Thread |
Display Modes | |
|
|
![]() |
||||
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) |