Solved

coutning visible rows with filter

Posted on 2011-09-16
10
193 Views
Last Modified: 2012-05-12
Hi guys,

Going crazy here,  I'm starting with a large excel data sheet.
Sheet attached...  Then for a array I have with account numbers... I use the filter for each account.   So it only displays the 1 accounts information on the screen.

Then I count the visible rows using
            counter = Selection.Columns(1).SpecialCells(xlCellTypeVisible).Rows.Count
                If counter > 1 Then

The weird thing is the first account I filter... it works accurately.  So for example, if I look at 1st account 05a-000016, it shows 2 rows... the header, and the information.
And the counter variable = 2 as it should

Then when I go to 2nd account, 05a-000091, it accurately filters information so I am now viewing 3 rows, the header and 2 lines of info.  However, using the same code, the counter states 1.

Any ideas why the counter works the 1st time, then the second time counter goes to 1?

thanks in advance!!!
Sub CreateGroupEmailCF()
Dim x As Integer
Dim counter As Integer
        Workbooks.Add
        ActiveWorkbook.SaveAs Filename:= _
                    "I:\Macro\Coffee\Recap_Email.xlsx", FileFormat:= _
                    xlOpenXMLWorkbook, CreateBackup:=False
        originalSheet.Activate
        
        For x = 1 To UBound(AcctListCF)
            With ActiveSheet
                .AutoFilterMode = False
                With .Range("A1:S1")
                    .AutoFilter
                    .AutoFilter Field:=5, Criteria1:=AcctListCF(x)
                End With
            End With
            Range("A1").Select
            Selection.CurrentRegion.Select
            counter = Selection.Columns(1).SpecialCells(xlCellTypeVisible).Rows.Count
                If counter > 1 Then
                    Selection.Copy Destination:=Workbooks("Recap_Email.xlsx").Sheets(1).Range("A65536").End(xlUp).Offset(1, 0)
                End If
        Next x
        
        Workbooks("Recap_Email.xlsx").Activate
        Range("a1").EntireRow.Delete
        Call FormatSheet

End Sub

Open in new window

test.xls
0
Comment
Question by:solarissf
[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
  • 4
  • 2
  • 2
  • +2
10 Comments
 
LVL 8

Expert Comment

by:ragnarok89
ID: 36551275
Could you not simply create a pivot table and read you values from that?
test-1.xls
0
 

Author Comment

by:solarissf
ID: 36551777
for formatting reasons I need to keep the layout in its original form
0
 
LVL 17

Expert Comment

by:andrewssd3
ID: 36552002
The reason is that SpecialCells is returning a range that has multiple areas, and Rows.Count is only counting the cells in the first area, i.e. the first contiguous range of cells.  You will need to loop through each Area in the selection and sum the counts for each. Something like this (not tested as I don't have all your code, but you can see the principle):

            Dim a As Excel.Range
            counter = 0
            For Each a In Selection.Columns(1).SpecialCells(xlCellTypeVisible)
                counter = counter + a.Rows.Count
            Next a

Open in new window

0
PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

 
LVL 17

Expert Comment

by:andrewssd3
ID: 36552052
Looking again, I suppose if Selection.Columns(1).SpecialCells(xlCellTypeVisible).Areas.Count > 1 then you know you need to do your copy as there must be at least 2 rows - it's only if there's one area that you need the count, if I'm reading your code right
0
 
LVL 17

Expert Comment

by:andrewssd3
ID: 36552150
Correction - line 3 should (crucially) read

            For Each a In Selection.Columns(1).SpecialCells(xlCellTypeVisible).Areas

Open in new window


Apologies
0
 
LVL 27

Accepted Solution

by:
Glenn Ray earned 250 total points
ID: 36553200
A much simpler method would be to use
WorksheetFunction.COUNTIF(selection, AcctListCF(x))

Here is code that would replace lines 18-20 in your original post:
 
Range("E1").Select
Selection.End(xlDown).Select
counter = WorksheetFunction.CountIf(Selection, AcctListCF(x))

Open in new window


All other behavior is preserved.
0
 
LVL 27

Expert Comment

by:Glenn Ray
ID: 36553226
Sorry...in my haste to post...missed the selection logic.  This code replaces lines 18-20
 
Range("E:E").Select
If AcctListCF(x) = "" Then
    counter = 0
Else
    counter = WorksheetFunction.CountIf(Selection, AcctListCF(x))
End If

Open in new window

0
 
LVL 17

Assisted Solution

by:andrewssd3
andrewssd3 earned 250 total points
ID: 36553595
Agreed COUNTIF would probably be a simpler solution - I was just answering the question!  Having said that, you would need some more logic to do the selection so the rows could be copied as they are in Line 22, which would mean running through each row and adding it to the selection using Application.Union if it matched.  I personally would probably still prefer to do that rather than using filter, but I don't think there's very much in it either way.
0
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 36559869
Since you are only looking at one column, just use:

counter = Selection.Columns(1).SpecialCells(xlCellTypeVisible).Count

which will work with discrete areas.
0
 

Author Comment

by:solarissf
ID: 36560864
ended up using countif... much easier solution I didnt think about before

thanks for all the help everyone!
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
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 …
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…

623 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