Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Counting the total visible rows that an autofilter produces

Posted on 2014-02-25
17
Medium Priority
?
281 Views
Last Modified: 2014-02-26
The following code needs to place the total visible rows produced by an autofilter to a specific spot on another sheet tab.  How would I modify the code to do this?

Sub Macro2()
'
    Sheets("CDPSRECRPT").Select
    Rows("1:1").Select
    Selection.AutoFilter
    ActiveWindow.LargeScroll ToRight:=1
    ActiveSheet.UsedRange.AutoFilter Field:=16, Operator:= _
        xlFilterValues, Criteria2:=Array(2, DateValue(Now))
'Need to count how many visible cells the autofilter produced.

    Sheets("MOT-MeasureData").Select
    Rows("2:2").Select 'insert a row under headers.
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    Rows("2:2").Select  'fix row height
    Selection.RowHeight = 14.4
    Range("A2").Select
    ActiveCell.FormulaR1C1 = "=NOW()"
    Range("A2").Select
    Selection.NumberFormat = "m/d/yyyy"
    Range("B2").Select
    
'This is where I need to insert the value from above, total visible cells
'from autofilter

End Sub

Open in new window

0
Comment
Question by:RWayneH
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 10
  • 6
17 Comments
 
LVL 53

Expert Comment

by:Rgonzo1971
ID: 39885688
Hi,

pls try

    RowCnt = ActiveSheet.UsedRange.Columns(1).SpecialCells(xlVisible).Count

Open in new window

Regards
0
 

Author Comment

by:RWayneH
ID: 39885712
So how does this value get placed in the other sheet tab?  Can you insert the needed code in the example?
0
 
LVL 93

Expert Comment

by:Patrick Matthews
ID: 39885778
RWayneH,

Keep in mind that SpecialCells can be unreliable when there are more than about 16,000 rows in your source data.  (The actual bug is more than 8192 discrete regions, for which you would need at least 16,384 rows.)

Can this be the case for you, or will you always be safely under that limit?

Patrick
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 53

Accepted Solution

by:
Rgonzo1971 earned 2000 total points
ID: 39885804
So I suppose you mean

Sub Macro2()
'
    Sheets("CDPSRECRPT").Select
    Rows("1:1").Select
    Selection.AutoFilter
    ActiveWindow.LargeScroll ToRight:=1
    ActiveSheet.UsedRange.AutoFilter Field:=16, Operator:= _
        xlFilterValues, Criteria2:=Array(2, DateValue(Now))
'Need to count how many visible cells the autofilter produced.
    RowCnt = ActiveSheet.UsedRange.Columns(1).SpecialCells(xlVisible).Count
    Sheets("MOT-MeasureData").Select
    Rows("2:2").Select 'insert a row under headers.
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    Rows("2:2").Select  'fix row height
    Selection.RowHeight = 14.4
    Range("A2").Select
    ActiveCell.FormulaR1C1 = "=NOW()"
    Range("A2").Select
    Selection.NumberFormat = "m/d/yyyy"
    Range("B2").Select
    Range("B2") = RowCnt
'This is where I need to insert the value from above, total visible cells
'from autofilter

End Sub

Open in new window

REgards
0
 

Author Comment

by:RWayneH
ID: 39885915
Yes exactly what I need, I am testing now... and the number of rows will only be a couple thousand rows. Thanks.
0
 

Author Comment

by:RWayneH
ID: 39886162
I would like to add an extension to this question.  How would I select and copy all the visible cells in column A? with the same autofilter on?  It gets the counts great, thanks, but I forgot that I need to select and copy column A too.
0
 

Author Comment

by:RWayneH
ID: 39886175
ending the macro on sheet CDPSRECRPT, with visible cells in column A selected and copied into the clipboard
0
 
LVL 53

Expert Comment

by:Rgonzo1971
ID: 39886192
Pls try

Sheets("CDPSRECRPT").Select
Sheets("CDPSRECRPT").UsedRange.Columns(1).SpecialCells(xlVisible).Copy

Open in new window

Regards
0
 

Author Comment

by:RWayneH
ID: 39886194
What is Ln2 telling it?
0
 

Author Comment

by:RWayneH
ID: 39886203
Is it putting them in the MOT-MeasureData sheet at cell B4?  I am pasting the list into SAP so I do not want to placed anywhere accept into the clipboad.
0
 
LVL 53

Expert Comment

by:Rgonzo1971
ID: 39886223
On my Edited Version

Sheets("CDPSRECRPT").Select
Sheets("CDPSRECRPT").UsedRange.Columns(1).SpecialCells(xlVisible).Copy 

Open in new window


It only copies the data into the clipboard
0
 

Author Comment

by:RWayneH
ID: 39886318
Sorry but it is grabbing the headers... how do I tell to grab everything but my header?
0
 

Author Comment

by:RWayneH
ID: 39887574
The headers cannot be part of the copy.. it is using the header and failing.  How do I tell the copy to exclude the header row.
0
 
LVL 53

Expert Comment

by:Rgonzo1971
ID: 39887968
pls try


Sheets("CDPSRECRPT").Select
Set UsdRng = Sheets("CDPSRECRPT").UsedRange
Set myRng = UsdRng.Offset(1).Resize(UsdRng.Rows.Count - 1, UsdRng.Columns.Count)
myRng.Columns(1).SpecialCells(xlVisible).Copy

Open in new window

Regards
0
 

Author Comment

by:RWayneH
ID: 39888405
Ok thanks..  does the count use the header row too?  Does that need to chg'd also?
0
 
LVL 53

Expert Comment

by:Rgonzo1971
ID: 39888439
That's right

so use instead
RowCnt = ActiveSheet.UsedRange.Columns(1).SpecialCells(xlVisible).Count -1

Open in new window

   
Regards
0
 

Author Closing Comment

by:RWayneH
ID: 39889247
EXCELlent!! -R-
0

Featured Post

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Having trouble getting your hands on Dynamics 365 Field Service or Project Service trial? Worry No More!!!
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

609 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question