asked on

coutning visible rows with filter

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

ragnarok89

Could you not simply create a pivot table and read you values from that?
test-1.xls

Stephen Forero

ASKER

for formatting reasons I need to keep the layout in its original form

andrewssd3

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

andrewssd3

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

andrewssd3

Correction - line 3 should (crucially) read

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

Open in new window

Apologies

ASKER CERTIFIED SOLUTION

Glenn Ray

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Glenn Ray

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

SOLUTION

andrewssd3

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Rory Archibald

Since you are only looking at one column, just use:

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

which will work with discrete areas.

Stephen Forero

ASKER

ended up using countif... much easier solution I didnt think about before

thanks for all the help everyone!