# 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

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 ```message edited by DerbyDad03

#1
May 4, 2019 at 20:26:47

#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?

#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.

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.

#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

#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.message edited by DerbyDad03

#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?

#8
May 8, 2019 at 19:03:05
 I believe that you should be able to do something with SUMPRODUCT.DAGS VBA countif filtered listLook for things like this:https://www.ozgrid.com/forum/forum/...

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

#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```

#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.message edited by DerbyDad03

 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 ```message edited by DerbyDad03