471,893 Members | 1,486 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 471,893 software developers and data experts.

Help with counting visible rows after filter in excel

I am trying to set up a prompt to print only if filtered rows are visible. I need to count the visible rows after the filter is applied (not including the header). I have tried various combinations but nothing seems to work every time.

Here is what I have:

Expand|Select|Wrap|Line Numbers
  1. ActiveSheet.Range("$A$1:$BU$87").AutoFilter Field:=17, Criteria1:="="
  2. ActiveSheet.Range("$A$1:$BU$87").AutoFilter Field:=1, Criteria1:="="
  3.     If ActiveSheet.AutoFilter.Range.Columns(2).SpecialCells(xlCellTypeVisible).Cells.Count - 1 > 1 Then
  4.     Yprint = MsgBox("There are new patients to enroll.  Do you want to print today's list?", vbYesNo + vbQuestion, "Print")
  5.     If Yprint = vbYes Then
  6.     ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True, IgnorePrintAreas:=False
  7. End If
  8. Else: MsgBox ("No new patients today")
  9. End If
Jul 7 '16 #1
2 5927
32,470 Expert Mod 16PB
If you use Ctrl-End when there are no rows except the header then the .Row of that resultant cell will be 1 (or whatever row your header is on).
Expand|Select|Wrap|Line Numbers
  1. If ActiveCell.SpecialCells(xlLastCell).Row = 1 Then
  2.     Debug.Print "No Rows";
  3. Else
  4.     Debug.Print "Some Rows";
  5. End If
NB. Please only use indenting in code if you do it in a way that makes sense. Indented code in the wrong places is even harder to read and work with than completely non-indented code - which is more than bad enough.
Jul 7 '16 #2
Use subtotal with the count function to get that. and subtract 1 to remove the header.

Expand|Select|Wrap|Line Numbers
  1. dim count as long
  3. count = worksheetfunction.subtotal(103, ActiveSheet.Range("$A$1:$BU$87").columns(1)) - 1
Hope it helps
Jul 8 '16 #3

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

reply views Thread by YellowAndGreen | last post: by
reply views Thread by zermasroor | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.