Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Autofilter and Cell Count
I have the following code that uses the "Autofilter" method to sort dates
(Sdate and Edate). After it filters the data, I use a counter to count the number of occurrences and use a msgbox to display the number of records. The problem is that the numbers don't seem to jive with the #records shown at the bottom of the screen. I even set an initial number to the counter (cnt =1 ) to help it along, but the count is still very erratic. Please help. Sub DateDifferences() Dim LastRow As Long, Row As Long Dim cnt As Long Dim SDate As String Dim EDate As String SDate = InputBox("Enter Starting Date as m/d/yyyy") EDate = InputBox("Enter Ending Date as m/d/yyyy") ActiveSheet.Range("L3:L10000").Select Selection.AutoFilter _ Field:=1, Criteria1:="=" & SDate, Operator:=xlAnd _ , Criteria2:="<=" & EDate cnt = 1 LastRow = Range("A10000").End(xlUp).Row For Row = 1 To LastRow If (Cells(Row, 12).Value) = SDate And Cells(Row, 12).Value <= EDate Then cnt = cnt + 1 Debug.Print cnt End If Next Row MsgBox cnt Selection.AutoFilter End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Autofilter and Cell Count
Better to just determine the number of visible rows:
MsgBox Range("A2", Range("A65536").End(xlUp)).SpecialCells(xlCellType Visible).Count I started with A2 assuming there's a header in A1. This assumes at least one visible row. -- Jim "asmenut" wrote in message ... |I have the following code that uses the "Autofilter" method to sort dates | (Sdate and Edate). After it filters the data, I use a counter to count the | number of occurrences and use a msgbox to display the number of records. The | problem is that the numbers don't seem to jive with the #records shown at the | bottom of the screen. | I even set an initial number to the counter (cnt =1 ) to help it along, but | the count is still very erratic. Please help. | | Sub DateDifferences() | | Dim LastRow As Long, Row As Long | Dim cnt As Long | Dim SDate As String | Dim EDate As String | | | SDate = InputBox("Enter Starting Date as m/d/yyyy") | EDate = InputBox("Enter Ending Date as m/d/yyyy") | | | ActiveSheet.Range("L3:L10000").Select | Selection.AutoFilter _ | Field:=1, Criteria1:="=" & SDate, Operator:=xlAnd _ | , Criteria2:="<=" & EDate | cnt = 1 | LastRow = Range("A10000").End(xlUp).Row | For Row = 1 To LastRow | If (Cells(Row, 12).Value) = SDate And Cells(Row, 12).Value <= EDate Then | cnt = cnt + 1 | Debug.Print cnt | End If | | Next Row | MsgBox cnt | Selection.AutoFilter | | End Sub | |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
AutoFilter and row count | Excel Discussion (Misc queries) | |||
Count Not Appearing when using AutoFilter? | Excel Worksheet Functions | |||
Autofilter record count | Excel Discussion (Misc queries) | |||
Count of results in AutoFilter | Excel Programming | |||
autofilter range count | Excel Programming |