Autofilter and Cell Count

[asmenut]

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

 

[Jim Rech]

Better to just determine the number of visible rows:

MsgBox Range("A2",
Range("A65536").End(xlUp)).SpecialCells(xlCellTypeVisible).Count

I started with A2 assuming there's a header in A1. This assumes at least
one visible row.

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