Problem with getting row count using SpecialCells in filtered excel sheet

[sharshra]

I want to count the number of rows after filtering thru vb.net code. I have written the following simple function to return row count. But, it is behaving wierdly. It is working correctly, but in some cases it is returning some randon number. Can anybody help to get this fixed or suggest a better solution?

Private Function AccessSheetFiltered (ByVal dteFromDate As Date, ByVal dteTo Date As Date) As Integer
(declarations......
set range..........)
    With xlsDynRange
        .AutoFilter(Field:=1, _
                        Criteria1:=">=" & dteFromDate, _
                        Operator:=Excel.XlAutoFilterOperator.xlAnd, _
                        Criteria2:="<=" & dteToDate)
        xlsFilteredRangeInFunction = xlsWorksheet.Range("A:A").SpecialCells(Excel.XlCellType.xlCellTypeVisible)
            
        AccessSheetFiltered = xlsFilteredRangeInFunction.Range("A:A").SpecialCells(Excel.XlCellType.xlCellTypeVisible).Count
    End With
End Function

 

[sharshra]

I had tried the option suggested by Mike. But, intellisence was not picking SpecialCells when I put dot after Columns(1). However, I just typed the entire line & it works. Probably, relying on intellisence stopped me trying other options. It was resolved on 1st Apr itself ......thanks to all resouceful guides like Mike in the forum. Apologies for responding late.

Just curious....... what could be the reason for intellisence not picking SpecialCells?

 

[ukmikeb]

Just curious....... what could be the reason for intellisence not picking SpecialCells?

Intellisense seems to be quite flaky in its operation at times and any one of :-
1, Variable not defined/mis-spelt
2, Amending a previously keyed/copied line
3, Mis-spelt keyword

causes it not to activate.

As you have guessed it is best not relied upon!

hth