Solved Counting rows when using a filter

May 3, 2019 at 16:35:32
Specs: Windows 7
I have this code provided to me from DerbyDad03 that worked perfectly for the worksheet I needed it for. I tried applying the same code to a new sheet that has filters and discovered that this doesn't work because it is using the range row number from the NumRows variable which is a total count of the rows but when data is filtered the row numbers are not sequential. When the data is filtered in go from row 10 to row 15 to row 100 to row 230 depending on filter. So if the xlDown counts 10 rows the row range max is row 10 but the data my sit in row 200.

Any suggestion on how to0 apply this code when data is filtered?

NumRows = Range("A10", Range("A10").End(xlDown)).Rows.Count
'      Range("A10").Select
For x = 2 To NumRows + 1
'At each name, use COUNTIF to look upwards and see if the name already exists.
'If not, add name, semi-colon and space to temporary name list
         If WorksheetFunction.CountIf(Range("E10:E" & x), Range("E" & x)) < 2 Then
             nameList_tmp = nameList_tmp & Range("E" & x) & "; "
         End If
      Next
'Strip last semi-colon & space off of temp list
          namelist = Left(nameList_tmp, Len(nameList_tmp) - 2)


See More: Counting rows when using a filter

Reply ↓  Report •

✔ Best Answer
May 9, 2019 at 09:30:06
mecerrato:

If you want to stay within VBA, then go back to the Array method I suggested in the other thread and then extract the unique elements from the array to build the email string.

There are a couple of methods of extracting the unique elements shown here:

https://stackoverflow.com/questions...

If you don't mind being clunky, you can use an Excel range to hold all of the "visible" email addresses and then use the original COUNTIF method since we know it works on an unfiltered list.

Pick a safe spot to store the visible values from the filtered range, then loop though that unfiltered range using COUNTIF to build your email address string. Just make sure that the location you choose will never get filtered. The following code will grab all the visible values from the filtered list and put them on another sheet. Once that list is built, the same COUNTIF method that we used before will be used on this new, unfiltered range.

Sub HardList()

'Move visible email addresses to Sheet2.ColumnA
Sheets(2).Cells.ClearContents
  lastSrcRw = Sheets(1).Cells(Rows.Count, 2).End(xlUp).Row
     For Each cell In Sheets(1).Range("E11:E" & lastSrcRw).SpecialCells(xlCellTypeVisible)
        dstRw = dstRw + 1
        cell.Copy Sheets(2).Range("A" & dstRw)
     Next
  
  
'Loop through Sheet2 list, extract unique addresses
  lastTmpRw = Sheets(2).Cells(Rows.Count, 1).End(xlUp).Row
     For tmpRw = 1 To lastTmpRw
         If WorksheetFunction.CountIf(Sheets(2).Range("A1:A" & tmpRw), _
            Sheets(2).Range("A" & tmpRw)) < 2 Then
               addylist_tmp = addylist_tmp & Sheets(2).Range("A" & tmpRw).Value & "; "
         End If
     Next tmpRw

'Clean up temp addylist
     addylist = Left(addylist_tmp, Len(addylist_tmp) - 2)
     MsgBox addylist
     
End Sub

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code

message edited by DerbyDad03



#1
May 4, 2019 at 20:26:47
I don't have access to Excel this weekend. Google found this...maybe it will help

https://stackoverflow.com/questions...

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code


Reply ↓  Report •

#2
May 5, 2019 at 17:07:45
DerbyDad03 I just realized I didn’t ask the right question. I don’t need to count the rows I need to know the row number in order to collect the correct data. So if I have 10 rows visible they will have non-contiguous row numbers because of the filter.

Should I ask a new question?


Reply ↓  Report •

#3
May 7, 2019 at 02:52:06
I haven't forgotten about you, just been very busy.

You might want to DAGS for info on using COUNTIF with SpecialCells(xlCellTypeVisible)

I think your answer might involve that, but I'm not 100% sure.

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code


Reply ↓  Report •

Related Solutions

#4
May 7, 2019 at 12:23:03
Now that I've had a chance to look at this a little more carefully, I'm confused by this latest piece of code.

In the code from the previous thread, you used this:

NumRows = Range("A2", Range("A2").End(xlDown)).Rows.Count

and then this:


For x = 2 To NumRows + 1
    If WorksheetFunction.CountIf(Range("C2:C" & x), Range("C" & x)) < 2 Then

The Range reference for both of those instructions start in Row 2 and the Row variable x starts at 2. That makes sense.

In this thread you used:

NumRows = Range("A2", Range("A2").End(xlDown)).Rows.Count

and then this:

For x = 2 To NumRows + 1
    If WorksheetFunction.CountIf(Range("E10:E" & x), Range("E" & x)) < 2 Then
                                        ^^^^^

In this case the the NumRows range starts at Row 2 as does the Row variable x, but the COUNTIF range starts at Row 10.

The first time through the loop, that instruction will look like this:

If WorksheetFunction.CountIf(Range("E10:E2"), Range("E2")) < 2 Then

The next time it will be:

If WorksheetFunction.CountIf(Range("E10:E3"), Range("E3")) < 2 Then

Are those Range references correct? I don't understand the offset from Row 2 to Row 10.

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code


Reply ↓  Report •

#5
May 7, 2019 at 19:50:18
After doing some more research (DAGS) I decided to scrap that code.

I was able to apply this code and it effectively identifies each row number of the filtered rows.

But I am getting a Runtime error 5 invalid procedure call or argument, the debugger stops at

namelist = Left(namelist_tmp, Len(namelist_tmp) - 2)

Once I can get the code working I can use the variable namelist in my .to code for the automated email.

Sub filteredstuff()
Dim lastRow As Long, myrange As Range
lastRow = Cells(Rows.Count, 2).End(xlUp).Row
For Each myrange In Range("E11:E" & lastRow).SpecialCells(xlCellTypeVisible)
'At each name, use COUNTIF to look upwards and see if the name already exists.
'If not, add name, semi-colon and space to temporary name list
         If WorksheetFunction.CountIf(Range("E11:E" & myrange.Row), _
         Range("E" & myrange.Row)) < 2 Then
             namelist_tmp = namelist_tmp & Range("E" & myrange.Row).Value & "; "
         End If
'Strip last semi-colon & space off of temp list
             namelist = Left(namelist_tmp, Len(namelist_tmp) - 2)
Next myrange
MsgBox namelist
End Sub

message edited by mecerrato


Reply ↓  Report •

#6
May 8, 2019 at 03:31:41
Have you single stepped through the code to see if the namelist_tmp variable is being built?

I'm not sure that COUNTIF works for a non-contiguous range. You may need to built an array of all values in the filtered range and then extract the unique values. That gets complicated but there are example subs out on the web.

I am swamped with real work and don't have the time to get into this very deep right now.

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code

message edited by DerbyDad03


Reply ↓  Report •

#7
May 8, 2019 at 18:32:01
I think you hit the nail on the head.
So after some testing I realized that the code works perfectly when the data is not filtered, in other words I am showing all the data. Once I filter it and the row numbers are no longer consecutive I get the Run-time error 5 Invalid Procedure call or argument at the namelist_tmp code: namelist = Left(namelist_tmp, Len(namelist_tmp) - 2)

Any suggestions?


Reply ↓  Report •

#8
May 8, 2019 at 19:03:05
I believe that you should be able to do something with SUMPRODUCT.

DAGS VBA countif filtered list

Look for things like this:

https://www.ozgrid.com/forum/forum/...

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code


Reply ↓  Report •

#9
May 8, 2019 at 23:09:20
I could not figure it out, I will keep searching

Reply ↓  Report •

#10
May 9, 2019 at 06:35:41
Not sure if this will help but heres an idea....

if you can capture the row of each visible filter row then you can use the row number to do a lookup??
Sorry I don't currently have the time to look properly as this today, but if you can explain exactly what you are trying to achieve maybe I can tomorrow..

My Data looks like this

            A
1    a
2    b
3    b
4    b
5    a
6    b
7    a
8    b
9    b
10  a
11  a
12  b

When I filter on a's and run the code, it will print in the immediate window the row numbers of all visible cells (where a is present) 

1, 5, 7, 10, 11


Sub test()
    
    Dim bcell As Range
    
    lastRow = Cells(Rows.Count, 1).End(xlUp).Row
    
    For Each bcell In Range("A2:A" & lastRow).SpecialCells(xlCellTypeVisible)
        Debug.Print bcell.Row
    Next bcell
End Sub


Reply ↓  Report •

#11
May 9, 2019 at 08:52:31
AWTL:

Grabbing the row numbers works fine, but that's not the ultimate goal.

Let's say that you filtered on Column A, but want to extract the values from Column E, which in this case would contain email addresses. Having the Row numbers helps, but the ultimate goal is to then to use the email addresses to build a string that can be used in the To: field of an email. So far we're still good but there is a significant criteria on top of that:

The goal is to extract only the unique email addresses in those visible cells. That's where we're stuck. In an unfiltered list, we can loop through the range and use COUNTIF to look upwards and ignore any email addresses that have already been found.

However, once we have a Filtered list the Rows become non-contiguous and the COUNTIF function doesn't work.

I've seen various strategies using SUMPRODUCT, others using an Array and then extracting the unique elements, others using the AREAS property. Unfortunately, I haven't been able to find a large enough chunk of time to noddle this through to completion.

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code

message edited by DerbyDad03


Reply ↓  Report •

#12
May 9, 2019 at 09:30:06
✔ Best Answer
mecerrato:

If you want to stay within VBA, then go back to the Array method I suggested in the other thread and then extract the unique elements from the array to build the email string.

There are a couple of methods of extracting the unique elements shown here:

https://stackoverflow.com/questions...

If you don't mind being clunky, you can use an Excel range to hold all of the "visible" email addresses and then use the original COUNTIF method since we know it works on an unfiltered list.

Pick a safe spot to store the visible values from the filtered range, then loop though that unfiltered range using COUNTIF to build your email address string. Just make sure that the location you choose will never get filtered. The following code will grab all the visible values from the filtered list and put them on another sheet. Once that list is built, the same COUNTIF method that we used before will be used on this new, unfiltered range.

Sub HardList()

'Move visible email addresses to Sheet2.ColumnA
Sheets(2).Cells.ClearContents
  lastSrcRw = Sheets(1).Cells(Rows.Count, 2).End(xlUp).Row
     For Each cell In Sheets(1).Range("E11:E" & lastSrcRw).SpecialCells(xlCellTypeVisible)
        dstRw = dstRw + 1
        cell.Copy Sheets(2).Range("A" & dstRw)
     Next
  
  
'Loop through Sheet2 list, extract unique addresses
  lastTmpRw = Sheets(2).Cells(Rows.Count, 1).End(xlUp).Row
     For tmpRw = 1 To lastTmpRw
         If WorksheetFunction.CountIf(Sheets(2).Range("A1:A" & tmpRw), _
            Sheets(2).Range("A" & tmpRw)) < 2 Then
               addylist_tmp = addylist_tmp & Sheets(2).Range("A" & tmpRw).Value & "; "
         End If
     Next tmpRw

'Clean up temp addylist
     addylist = Left(addylist_tmp, Len(addylist_tmp) - 2)
     MsgBox addylist
     
End Sub

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code

message edited by DerbyDad03


Reply ↓  Report •

#13
May 12, 2019 at 08:13:05
This worked great, I hid the sheet so not really a big deal todo it this way, thanks for sticking with me, I really appreciate your help :-)

Reply ↓  Report •

Ask Question