none
Autofilter missing filtered data RRS feed

  • Question

  • Hi,

    I am filtering a huge spreadsheet (450,000) rows & (42) Columns for only one criteria in column "AA" - I am missing many occurrences. My data has hundreds of empty rows and I know that the filter will only look down as far as the row just above the empty row(s). I have selected all data before applying the filter. Is there a macro solution  for this issue?

    Thanks in advance!




    • Edited by E.Hanna Thursday, September 7, 2017 3:09 PM
    Thursday, September 7, 2017 2:48 PM

All replies

  • Please check if you have deleted only data in stead of the row where you see the empty rows.
    Remove the filter and filter it again, some of the other column might be filtered which gives less record.
    Please check if there is any macro.
    http://www.excel-easy.com/vba/create-a-macro.html
    Thursday, September 7, 2017 3:11 PM
  • Thanks for your reply, none of these scenarios apply.
    Thursday, September 7, 2017 3:14 PM
  • E.
    re:  filter count

    Maybe...
    "Excel specifications and limits"
     http://office.microsoft.com/en-us/excel-help/excel-specifications-and-limits-HP010342495.aspx?CTT=1

    "Items displayed in filter drop-down lists10,000"

     '---
    Jim Cone
    Portland, Oregon USA
    https://goo.gl/IUQUN2 (Dropbox)
    (free & commercial excel add-ins & workbooks)

    • Edited by James Cone Thursday, September 7, 2017 4:52 PM
    Thursday, September 7, 2017 4:51 PM
  • Thanks! I understand the reason behind it. Is there a way to get all the data? I also need to repeat the process several times.
    • Edited by E.Hanna Thursday, September 7, 2017 6:27 PM
    Thursday, September 7, 2017 6:26 PM
  • E.
    re: work around for filter limit

    I don't believe you are going to find anything built-in to do what you want.

    VBA code could do it, but with 450,000 cells to interrogate, you might have to go to lunch while the code runs. (or maybe not)
    The code could flag those cells containing the desired item(s) in an adjoining column.
    Then a sort would be done on the data using the new column as the key.
    You would end up with the desired rows grouped together.
    The interesting part would be creating something that is usable by the summer intern majoring in social justice.

    One of the utilities in my commercial add-in "XL Professional" does something similar,
    but it colors, clears or deletes the rows containing (or not containing) the search item, not exactly what you need.

    '---
    Jim Cone
    Portland, Oregon USA
    https://goo.gl/IUQUN2 (Dropbox)
    (free & commercial excel add-ins & workbooks)


    • Edited by James Cone Thursday, September 7, 2017 11:41 PM slight reword
    Thursday, September 7, 2017 8:05 PM
  • Hello,

    You may create an array to store the data and loop through all cells in the column and check if it is in the array. If it could not be found, then add the item into the array. Then you could get the list.

    E.g.

    Sub test()
    Dim cell As Range
    Dim ary() As Variant
    ReDim ary(0)
    For Each cell In Range("A2:A100")
    If UBound(Filter(ary(), cell.Value)) < 0 Then
    ReDim Preserve ary(i)
    ary(i) = cell.Value
    i = i + 1
    End If
    Next
    End Sub

    Regards,

    Celeste


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, September 8, 2017 6:17 AM
    Moderator
  • Hi E.Hanna,

    >> I have selected all data before applying the filter

    How did you select the all data? Do you mean you select the data in Column AA or entire worksheet? Does the filter drop-down items extend the limit?

    Will this issue exist if the spreadsheet contains 10 rows with empty rows? If your issue is related with empty row, I suggest you follow Click Top and Left Column and Row( or you could Click Column AA), then Click Filter, the drop-down will contains all the values in Column AA.

    Best Regards,

    Edward


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, September 12, 2017 2:38 AM
  • Re:  work around for filter limit

    The XL Professional add-in (release 3.61) can now substitute for the worksheet Filter when results exceed 10,000 rows.
    It is for xl97 thru xl2010 only and should be available at the Dropbox website by the end of the week (Sep 15, 2017).

    '---
    Jim Cone
    Portland, Oregon USA
    https://goo.gl/IUQUN2 (Dropbox)
    (free & commercial excel add-ins & workbooks)



    • Edited by James Cone Tuesday, September 12, 2017 4:17 AM
    Tuesday, September 12, 2017 4:09 AM
  • Thanks for your reply - The issue is due to the humungous size of data + hundreds of empty rows. No, the issue doesn't exist when I have few rows. I tried Column AA and entire worksheet → same problem.
    Tuesday, September 12, 2017 2:24 PM
  • Hi E.Hanna,

    Thanks for more information. It seems you hit the limitation of filter drop-down items. We need to implement our own filter function instead of using built-in Filter.

    Do you have any trouble to try suggestion from Celeste and James?

    Best Regards,

    Edward


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, September 13, 2017 3:03 AM
  • Hi Edward,

    I am adjusting a macro to help me with filtering.

    Thank you,

    Wednesday, September 13, 2017 3:10 PM
  • Hi E.Hanna,

    Great. If you have any trouble to achieve this, please feel free to let us know.

    If you resolved, it would be appreciated if you share us the solution.

    Hope your issue will be resolved soon.

    Best Regards,

    Edward


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, September 14, 2017 1:43 AM