[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 282
  • Last Modified:

Counting the total visible rows that an autofilter produces

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
RWayneH
Asked:
RWayneH
  • 10
  • 6
1 Solution
 
Rgonzo1971Commented:
Hi,

pls try

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

Open in new window

Regards
0
 
RWayneHAuthor Commented:
So how does this value get placed in the other sheet tab?  Can you insert the needed code in the example?
0
 
Patrick MatthewsCommented:
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
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

 
Rgonzo1971Commented:
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
 
RWayneHAuthor Commented:
Yes exactly what I need, I am testing now... and the number of rows will only be a couple thousand rows. Thanks.
0
 
RWayneHAuthor Commented:
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
 
RWayneHAuthor Commented:
ending the macro on sheet CDPSRECRPT, with visible cells in column A selected and copied into the clipboard
0
 
Rgonzo1971Commented:
Pls try

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

Open in new window

Regards
0
 
RWayneHAuthor Commented:
What is Ln2 telling it?
0
 
RWayneHAuthor Commented:
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
 
Rgonzo1971Commented:
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
 
RWayneHAuthor Commented:
Sorry but it is grabbing the headers... how do I tell to grab everything but my header?
0
 
RWayneHAuthor Commented:
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
 
Rgonzo1971Commented:
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
 
RWayneHAuthor Commented:
Ok thanks..  does the count use the header row too?  Does that need to chg'd also?
0
 
Rgonzo1971Commented:
That's right

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

Open in new window

   
Regards
0
 
RWayneHAuthor Commented:
EXCELlent!! -R-
0

Featured Post

2018 Annual Membership Survey

Here at Experts Exchange, we strive to give members the best experience. Help us improve the site by taking this survey today! (Bonus: Be entered to win a great tech prize for participating!)

  • 10
  • 6
Tackle projects and never again get stuck behind a technical roadblock.
Join Now