vba code to count visible rows after autofiltering a table

E

epb613

New Member
Joined
Feb 18, 2010
Messages
14
I autofiltered a table. Now I want to count the remaining rows. I searched online and found the following code snippet, but it doesn't work. It should return 500, but it returns 1000 (it's counting cells, not rows). What's wrong? (If I take out the SpecialCells property, it returns 500 like it should, but obviously I need to leave that in.)

Code: 
MsgBox Range.Rows.SpecialCells(xlCellTypeVisible).Count

Thanks!
Pinny
 
Sort by date Sort by votes
epb613 said:
Marcelo, is there any reason you chose to use a For loop instead of the .Count method?
Click to expand...

As you have written...
The problem was that after using the SpecialCells method, you are returned a Range of non-continuous areas, which you can't use the Rows property on.
Click to expand...

Try a test with a table like this

<TABLE style="WIDTH: 144pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=192><COLGROUP><COL style="WIDTH: 48pt" span=3 width=64><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #95b3d7 0.5pt solid; BORDER-LEFT: #95b3d7 0.5pt solid; WIDTH: 48pt; FONT-FAMILY: Calibri; BACKGROUND: #4f81bd; HEIGHT: 15pt; COLOR: white; FONT-SIZE: 11pt; BORDER-TOP: #95b3d7 0.5pt solid; FONT-WEIGHT: 700; BORDER-RIGHT: #d4d0c8; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #4F81BD none" height=20 width=64>Field1</TD><TD style="BORDER-BOTTOM: #95b3d7 0.5pt solid; BORDER-LEFT: #d4d0c8; WIDTH: 48pt; FONT-FAMILY: Calibri; BACKGROUND: #4f81bd; COLOR: white; FONT-SIZE: 11pt; BORDER-TOP: #95b3d7 0.5pt solid; FONT-WEIGHT: 700; BORDER-RIGHT: #d4d0c8; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #4F81BD none" width=64>Field2</TD><TD style="BORDER-BOTTOM: #95b3d7 0.5pt solid; BORDER-LEFT: #d4d0c8; WIDTH: 48pt; FONT-FAMILY: Calibri; BACKGROUND: #4f81bd; COLOR: white; FONT-SIZE: 11pt; BORDER-TOP: #95b3d7 0.5pt solid; FONT-WEIGHT: 700; BORDER-RIGHT: #95b3d7 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #4F81BD none" width=64>Field3</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #95b3d7 0.5pt solid; BORDER-LEFT: #95b3d7 0.5pt solid; FONT-FAMILY: Calibri; BACKGROUND: #dbe5f1; HEIGHT: 15pt; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: #d4d0c8; FONT-WEIGHT: 400; BORDER-RIGHT: #d4d0c8; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #DBE5F1 none" height=20>A</TD><TD style="BORDER-BOTTOM: #95b3d7 0.5pt solid; BORDER-LEFT: #d4d0c8; FONT-FAMILY: Calibri; BACKGROUND: #dbe5f1; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: #d4d0c8; FONT-WEIGHT: 400; BORDER-RIGHT: #d4d0c8; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #DBE5F1 none" align=right>10</TD><TD style="BORDER-BOTTOM: #95b3d7 0.5pt solid; BORDER-LEFT: #d4d0c8; FONT-FAMILY: Calibri; BACKGROUND: #dbe5f1; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: #d4d0c8; FONT-WEIGHT: 400; BORDER-RIGHT: #95b3d7 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #DBE5F1 none" align=right>29</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #95b3d7 0.5pt solid; BORDER-LEFT: #95b3d7 0.5pt solid; BACKGROUND-COLOR: transparent; FONT-FAMILY: Calibri; HEIGHT: 15pt; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: #d4d0c8; FONT-WEIGHT: 400; BORDER-RIGHT: #d4d0c8; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none" height=20>B</TD><TD style="BORDER-BOTTOM: #95b3d7 0.5pt solid; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; FONT-FAMILY: Calibri; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: #d4d0c8; FONT-WEIGHT: 400; BORDER-RIGHT: #d4d0c8; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none" align=right>11</TD><TD style="BORDER-BOTTOM: #95b3d7 0.5pt solid; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; FONT-FAMILY: Calibri; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: #d4d0c8; FONT-WEIGHT: 400; BORDER-RIGHT: #95b3d7 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none" align=right>30</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #95b3d7 0.5pt solid; BORDER-LEFT: #95b3d7 0.5pt solid; FONT-FAMILY: Calibri; BACKGROUND: #dbe5f1; HEIGHT: 15pt; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: #d4d0c8; FONT-WEIGHT: 400; BORDER-RIGHT: #d4d0c8; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #DBE5F1 none" height=20>A</TD><TD style="BORDER-BOTTOM: #95b3d7 0.5pt solid; BORDER-LEFT: #d4d0c8; FONT-FAMILY: Calibri; BACKGROUND: #dbe5f1; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: #d4d0c8; FONT-WEIGHT: 400; BORDER-RIGHT: #d4d0c8; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #DBE5F1 none" align=right>12</TD><TD style="BORDER-BOTTOM: #95b3d7 0.5pt solid; BORDER-LEFT: #d4d0c8; FONT-FAMILY: Calibri; BACKGROUND: #dbe5f1; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: #d4d0c8; FONT-WEIGHT: 400; BORDER-RIGHT: #95b3d7 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #DBE5F1 none" align=right>31</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #95b3d7 0.5pt solid; BORDER-LEFT: #95b3d7 0.5pt solid; BACKGROUND-COLOR: transparent; FONT-FAMILY: Calibri; HEIGHT: 15pt; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: #d4d0c8; FONT-WEIGHT: 400; BORDER-RIGHT: #d4d0c8; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none" height=20>B</TD><TD style="BORDER-BOTTOM: #95b3d7 0.5pt solid; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; FONT-FAMILY: Calibri; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: #d4d0c8; FONT-WEIGHT: 400; BORDER-RIGHT: #d4d0c8; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none" align=right>13</TD><TD style="BORDER-BOTTOM: #95b3d7 0.5pt solid; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; FONT-FAMILY: Calibri; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: #d4d0c8; FONT-WEIGHT: 400; BORDER-RIGHT: #95b3d7 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none" align=right>32</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #95b3d7 0.5pt solid; BORDER-LEFT: #95b3d7 0.5pt solid; FONT-FAMILY: Calibri; BACKGROUND: #dbe5f1; HEIGHT: 15pt; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: #d4d0c8; FONT-WEIGHT: 400; BORDER-RIGHT: #d4d0c8; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #DBE5F1 none" height=20>A</TD><TD style="BORDER-BOTTOM: #95b3d7 0.5pt solid; BORDER-LEFT: #d4d0c8; FONT-FAMILY: Calibri; BACKGROUND: #dbe5f1; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: #d4d0c8; FONT-WEIGHT: 400; BORDER-RIGHT: #d4d0c8; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #DBE5F1 none" align=right>14</TD><TD style="BORDER-BOTTOM: #95b3d7 0.5pt solid; BORDER-LEFT: #d4d0c8; FONT-FAMILY: Calibri; BACKGROUND: #dbe5f1; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: #d4d0c8; FONT-WEIGHT: 400; BORDER-RIGHT: #95b3d7 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #DBE5F1 none" align=right>33</TD></TR></TBODY></TABLE>

Filter criteria = "A"

<TABLE style="WIDTH: 144pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=192><COLGROUP><COL style="WIDTH: 48pt" span=3 width=64><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #95b3d7 0.5pt solid; BORDER-LEFT: #95b3d7 0.5pt solid; WIDTH: 48pt; FONT-FAMILY: Calibri; BACKGROUND: #4f81bd; HEIGHT: 15pt; COLOR: white; FONT-SIZE: 11pt; BORDER-TOP: #95b3d7 0.5pt solid; FONT-WEIGHT: 700; BORDER-RIGHT: #d4d0c8; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #4F81BD none" height=20 width=64>Field1</TD><TD style="BORDER-BOTTOM: #95b3d7 0.5pt solid; BORDER-LEFT: #d4d0c8; WIDTH: 48pt; FONT-FAMILY: Calibri; BACKGROUND: #4f81bd; COLOR: white; FONT-SIZE: 11pt; BORDER-TOP: #95b3d7 0.5pt solid; FONT-WEIGHT: 700; BORDER-RIGHT: #d4d0c8; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #4F81BD none" width=64>Field2</TD><TD style="BORDER-BOTTOM: #95b3d7 0.5pt solid; BORDER-LEFT: #d4d0c8; WIDTH: 48pt; FONT-FAMILY: Calibri; BACKGROUND: #4f81bd; COLOR: white; FONT-SIZE: 11pt; BORDER-TOP: #95b3d7 0.5pt solid; FONT-WEIGHT: 700; BORDER-RIGHT: #95b3d7 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #4F81BD none" width=64>Field3</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #95b3d7 0.5pt solid; BORDER-LEFT: #95b3d7 0.5pt solid; FONT-FAMILY: Calibri; BACKGROUND: #dbe5f1; HEIGHT: 15pt; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: #d4d0c8; FONT-WEIGHT: 400; BORDER-RIGHT: #d4d0c8; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #DBE5F1 none" height=20>A</TD><TD style="BORDER-BOTTOM: #95b3d7 0.5pt solid; BORDER-LEFT: #d4d0c8; FONT-FAMILY: Calibri; BACKGROUND: #dbe5f1; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: #d4d0c8; FONT-WEIGHT: 400; BORDER-RIGHT: #d4d0c8; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #DBE5F1 none" align=right>10</TD><TD style="BORDER-BOTTOM: #95b3d7 0.5pt solid; BORDER-LEFT: #d4d0c8; FONT-FAMILY: Calibri; BACKGROUND: #dbe5f1; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: #d4d0c8; FONT-WEIGHT: 400; BORDER-RIGHT: #95b3d7 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #DBE5F1 none" align=right>29</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #95b3d7 0.5pt solid; BORDER-LEFT: #95b3d7 0.5pt solid; BACKGROUND-COLOR: transparent; FONT-FAMILY: Calibri; HEIGHT: 15pt; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: #d4d0c8; FONT-WEIGHT: 400; BORDER-RIGHT: #d4d0c8; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none" height=20>A</TD><TD style="BORDER-BOTTOM: #95b3d7 0.5pt solid; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; FONT-FAMILY: Calibri; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: #d4d0c8; FONT-WEIGHT: 400; BORDER-RIGHT: #d4d0c8; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none" align=right>12</TD><TD style="BORDER-BOTTOM: #95b3d7 0.5pt solid; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; FONT-FAMILY: Calibri; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: #d4d0c8; FONT-WEIGHT: 400; BORDER-RIGHT: #95b3d7 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none" align=right>31</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #95b3d7 0.5pt solid; BORDER-LEFT: #95b3d7 0.5pt solid; FONT-FAMILY: Calibri; BACKGROUND: #dbe5f1; HEIGHT: 15pt; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: #d4d0c8; FONT-WEIGHT: 400; BORDER-RIGHT: #d4d0c8; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #DBE5F1 none" height=20>A</TD><TD style="BORDER-BOTTOM: #95b3d7 0.5pt solid; BORDER-LEFT: #d4d0c8; FONT-FAMILY: Calibri; BACKGROUND: #dbe5f1; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: #d4d0c8; FONT-WEIGHT: 400; BORDER-RIGHT: #d4d0c8; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #DBE5F1 none" align=right>14</TD><TD style="BORDER-BOTTOM: #95b3d7 0.5pt solid; BORDER-LEFT: #d4d0c8; FONT-FAMILY: Calibri; BACKGROUND: #dbe5f1; COLOR: black; FONT-SIZE: 11pt; BORDER-TOP: #d4d0c8; FONT-WEIGHT: 400; BORDER-RIGHT: #95b3d7 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #DBE5F1 none" align=right>33</TD></TR></TBODY></TABLE>

Run my macro and your macro and you can see the difference

M.
 
Upvote 0
But once you use Resize, you don't need to count rows anymore, only cells. After using Resize, why not use Count instead of the For loop? Isn't that all your For loop is doing anyway - counting cells?
 
Upvote 0
.Rows.count computes the number of rows (or cells since you resize to one column) of only the first Area.

The For loop computes all cells (ie rows after the resizing)

M.
 
Upvote 0
Marcelo Branco said:
Another one

Code: 
Sub Test()
    Dim rngTable As Range
    Dim rCell As Range, visibleRows As Long
    
    Set rngTable = ActiveSheet.ListObjects("Table_owssvr_1").Range
    
    For Each rCell In rngTable.Resize(, 1).SpecialCells(xlCellTypeVisible)
        visibleRows = visibleRows + 1
    Next rCell
    
    MsgBox visibleRows
End Sub

M.
Click to expand...

Marcelo - Thanks for this code, it was the only version that worked under every situation. I changed it a little, running against the ActiveSheet.UsedRange, and putting it into a function, but your code was a great starting point.

- Lou (The Average Bear)

Code: 
Sub TestTheFunction()
  MsgBox CountVisibleRows(1)
End Sub

Private Function CountVisibleRows(HdgRowCnt)
'******************************************************************************
'* - Count the number of visible data rows on the Active Sheet.
'* - Parameters:
'*   HdgRowCnt = the number of heading rows on the sheet
'* - Output:
'*   The result (the number of visible rows) is passed back to the calling
'*   statement in the function name.
'******************************************************************************
Dim rTable As Range
Dim rCell As Range
Dim visibleRows As Integer
    Set rTable = ActiveSheet.UsedRange
    For Each rCell In rTable.Resize(, 1).SpecialCells(xlCellTypeVisible)
      visibleRows = visibleRows + 1
    Next rCell
    CountVisibleRows = visibleRows - HdgRowCnt
End Function
 
Upvote 0
TheAverageBear said:
Marcelo - Thanks for this code, it was the only version that worked under every situation. I changed it a little, running against the ActiveSheet.UsedRange, and putting it into a function, but your code was a great starting point.

- Lou (The Average Bear)
Click to expand...

You are welcome. I'm glad the code has been helpful.

M.
 
Upvote 0
I have a function to count rows in a worksheet. The function runs inside of a form off a command button.
Places the results in a label. Can this function run without the button?.
I found this mentioned on this forum

<code>Function doubleMe(d)
Application.Volatile
doubleMe = d * 2
End Function</code>
 
Upvote 0
supergrass said:
Old thread, perennial problem.
Click to expand...

No kidding! This is a hard thing to nail down. I've been all over the Internet looking for solutions to this one.

supergrass said:
I could not get ActiveSheet.ListObjects(1).Range to work. Instead I used
Code: 
ActiveSheet.AutoFilter.Range
from VBA - Excel Filters - Count Visible Rows - Felicini

It worked. Unfortunately I have to use Excel 2003 here. I hope that helps somebody.
Click to expand...

Nice! and here's one I've used with great success; I modified it based on additional answers in a thread I found here:
excel vba - Row count on the Filtered data - Stack OverflowMine looks like this:

Code: 
<code>Application.WorksheetFunction.Subtotal(3, .Range("A2:A" & .Rows(.Rows.Count).End(xlUp).Row))</code>

This, of course, assumes the filtered range has its header row in row 1, and begins in the first column, but edit to fit your needs. You can also enter a specific filtered range if it's known, but...you know. Magic strings.

I hope this will help someone surfing for an answer. There are valuable ones in this thread.
 
Upvote 0
I know this is a late reply, but I found it a useful work-around
The Solution is non-vbause the WorkSheet Function Subtotal in a vacant cell on the worksheet

"=subtotal(3,your_range)

The SubTotal function counts only the visible (filtered) cells
the parameter 3 specifies the COUNTA function

References:
1.MSDN - SUBTOTAL function - Office Support
 
Upvote 0
Thanks to JoshHill and Bruce54. Although it looks kludgy at first, the beauty of the subtotal method is that it work when the autofilter returns zero rows. When autofilter returns zero rows, you cannot use SpecialCells without triggering an error, which then requires error handling.
 
Upvote 0
You must log in or register to reply here.

Similar threads

T
  • Question Question
Range as filter criteria
Replies
5
Views
580
Peter_SSs
Peter_SSs
U
  • Question Question
Excel Activate workbook1/sheet, Copy/paste row in workbook2/sheet, activate autofilter, return value and loop
Replies
0
Views
282
user29383902
U
B
  • Question Question
VBA code to cut and paste multiple filtered rows based on multiple criteria
Replies
2
Views
2K
Alex Blakenburg
A
U
  • Solved
Rewrite Code as a Sub to work on Active Sheet
Replies
2
Views
305
Skyybot
Skyybot
X
  • Question Question
VBA for extracting specific data from PDF Docs
Replies
3
Views
1K
xcellrodeo
X

Share this page

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