Problem with getting row count using SpecialCells in filtered excel sheet

sharshra

Active Member
Joined
Mar 20, 2013
Messages
270
Office Version
  1. 365
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?
Code:
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
 

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.
I have encountered strange problem. I am using the following code to get the row count. It works correctly for few. But, for few more, it is throwing the error "No cells were found". Getting this error, though there are rows. Not sure why this occurs. Any help please?
Code:
intRowsFiltered = FilterRange.Offset(1).SpecialCells(Excel.XlCellType.xlCellTypeVisible).Rows.Count()
 
Upvote 0
You are not using the code that Mike pointed you towards, hence your continuing issue. If you look at the code in post #5 of the indicated thread, it is different to what you quote.
 
Last edited:
Upvote 0
I have used the following code, but it is not returning correct row count. Not sure how range can be defined to have the code in line with Mike's reference post. Can anyone help please?
Code:
intRowsFiltered= xlsWorksheet.AutoFilter.Range.SpecialCells(Excel.XlCellType.xlCellTypeVisible).Count

Code given in the post # 5 from Mike's link is
Code:
NoofFiltrdRows = ActiveSheet.AutoFilter.Range.Columns(1).SpecialCells(xlCellTypeVisible).Count -1
 
Upvote 0
sharshra

Try this :-
Rich (BB code):
intRowsFiltered= xlsWorksheet.AutoFilter.Range.Columns(1).SpecialCells(Excel.XlCellType.xlCellTypeVisible).Count - 1

I trust that you don't have more records than the Integer type supports if your variable (intRowsFiltered) is of that type, otherwise change it to Long.

hth
 
Upvote 0
Mike,
SpecialCells is not recognized if I add Columns(1).

Code:
xlsAccessWorksheet.AutoFilter.Range.Offset(1).Rows.SpecialCells(Excel.XlCellType.xlCellTypeVisible).Count
returns total visible cells.
Code:
intRowsFiltered= (xlsAccessWorksheet.AutoFilter.Range.Offset(1).Rows.SpecialCells(Excel.XlCellType.xlCellTypeVisible).Count) / 9
I am dividing this by number of columns to get rows. Not a smart way, but it works. I am sure there must be better ways.
 
Upvote 0
I don't want to sound overly rude but why do you bother asking for help if you don't use it when it's been given?

You've been shown what you need but you are not applying it - this is what you should use with applying the Columns(1):

Code:
intRowsFiltered= xlsAccessWorksheet.AutoFilter.Range.Columns(1).SpecialCells(12).Count - 1

12 is the integer equivalent of xlCellTypeVisible
 
Upvote 0

Forum statistics

Threads
1,213,513
Messages
6,114,072
Members
448,546
Latest member
KH Consulting

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top