-
Mar 23rd, 2009, 01:59 PM
#1
Thread Starter
Junior Member
Counting rows with autofilter on
I usually use Selection.Rows.count to count the number of active rows....however if you are using an autofilter it will count all rows, not the visible rows.....can anyone help?
-
Mar 23rd, 2009, 02:23 PM
#2
Junior Member
Re: Counting rows with autofilter on
-
Mar 23rd, 2009, 10:37 PM
#3
Re: Counting rows with autofilter on
To count visible rows:
On worksheet, you can use
=SUBTOTAL(3, Range("A2:A1000"))
In VBA code:
VisibleRows = WorksheetFunction.Subtotal(3, Range("A2:A1000"))
You can also use
Range("A2:A1000").SpecialCells(xlCellTypeVisible)
-
Feb 2nd, 2012, 11:59 PM
#4
Member
Re: Counting rows with autofilter on
Rather than start a new thread, I am extending this thread as I have the same problem and the solutions suggested above do not work.
Open a new workbook, throw some values into column 1 of worksheet 1, and paste the following code in a new module. Make sure the values do not include "999" to make the error occur.
Code:
Private Sub Test()
Dim XL_Ws As Excel.Worksheet
Dim Last_Row As Long
Set XL_Ws = ThisWorkbook.Worksheets(1)
'Turn off any existing autofilter (otherwise row and col values may be zero)
XL_Ws.AutoFilterMode = False
'Determine last used row
If WorksheetFunction.CountA(XL_Ws.Cells) > 0 Then
Last_Row = XL_Ws.Cells.Find(What:="*", SearchDirection:=xlPrevious, SearchOrder:=xlByRows).Row
End If
'Apply autofilter to header
XL_Ws.Range("A1").AutoFilter Field:=1, Criteria1:=999
'Check if filtered range is empty
If XL_Ws.Range(Cells(2, 1), Cells(Last_Row, 1)).SpecialCells(xlCellTypeVisible).Rows.Count <> 0 Then
'Do main bit of code here
End If
End Sub
The problem is that the line which checks if there filtered range is empty generates an error itself! I get "Run-time error 1004: No cells were found."
I have tried several variations of this line of code to no avail:
Code:
If XL_Ws.Range(Cells(2, 1), Cells(Last_Row, 1)).SpecialCells(xlCellTypeVisible).Count <> 0 Then
If XL_Ws.Range(Cells(2, 1), Cells(Last_Row, 1)).SpecialCells(xlCellTypeVisible).Count - 1 <> 0 Then
If XL_Ws.Range(Cells(2, 1), Cells(Last_Row, 1)).SpecialCells(xlCellTypeVisible).Rows.Count Is Nothing Then
If IsError(XL_Ws.Range(Cells(2, 1), Cells(Last_Row, 1)).SpecialCells(xlCellTypeVisible).Rows.Count) Then
The worst part is that all the help forums I've read tell me that these solutions are supposed to work..... E.g.
www.contextures.com
www.ozgrid.com
www.ozgrid.com (2)
www.mrexcel.com
Etc.....
If possible, I would like to avoid using Excel-specific worksheet functions.
-
Feb 5th, 2012, 07:49 PM
#5
Member
Re: Counting rows with autofilter on
I still have not been able to solve this.
In addition to the above attempts, I have also tried capturing the error by evaluating the problematic code with functions like IsError and IsNull but VB seems to handle the expression first before the function, which also results in the same error. E.g.
Code:
If IsError(XL_Ws.Range(XL_Ws.Cells(2, 1), XL_Ws.Cells(Last_Row, 1)) _
.SpecialCells(xlCellTypeVisible).Rows.Count) Then
I don't understand what I'm doing wrong.
Last edited by Earlien; Feb 9th, 2012 at 07:02 PM.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|