469,127 Members | 1,371 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,127 developers. It's quick & easy.

Help with counting visible rows after filter in excel

lilp32
43
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
  10.  
Jul 7 '16 #1
2 5281
NeoPa
32,162 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
  2.  
  3. count = worksheetfunction.subtotal(103, ActiveSheet.Range("$A$1:$BU$87").columns(1)) - 1
  4.  
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

1 post views Thread by CARIGAR | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.