Excel autofilter limitation

I believe it is around the 1,000. However, it is not listed in the excel specifications. (See Onlie help on "Microsoft Excel specifications".)

Maybe someone has a better info source about Autofilter limit.

Regards,
Sebastien

AFAIK you can not filter more than 1000 different items/descriptors per column.

http://support.microsoft.com/default.aspx?scid=kb;en-us;295971

I believe he is saying that the autofilter does not take effect past row 1017, not that he has more than 1017 unique values in the column.

Is this the case?

WATYF

WATY, AFAIK autofilter handles more than 1000 rows, but most probably CSparks has more than 1000 unique values per column in one or more of the columns like dates, ID number, names, etc.

This is correct. It will only handle 1000 unique values... but this statement, "regardless of the column content", leads me to believe that he may have some kind of odd autofilter glitch going on that prevents it from taking effect past a certain row. I was asking him to clarify if that is the case.


WATYF

There are more than 1000 unique values in each of 21 columns.  I cannot figure out a viable workaround for the 1K limit.  The microsoft site (mentioned by cri) had the only idea I came up with--to split the data into separate worksheets and use autofilters on each page, but this means the user could conceivably have to make 8 different searches as well as navigate different pages in a workbook.  I am afraid my audience isn't really that sophisticated.  I have been trying to develop some sort of "find" macro, but am not having much luck since there are multiple entries for the same value....ideas?

You have 8000 unique values? Wow.

Yeah... that's kinda tough... although, you could just write a macro to hide rows that don't meet the criteria. (Be careful to match the type of data.. i.e. integer to integer, text to text, etc. This example assumes that the values in Column A are integers. You can also match text strings, etc.)

Obviously you could customize this in a million different ways.


******************************************************
Sub Test()

    Dim TmpVal As Integer
   
    ' removes previous "filter".
    Cells.EntireRow.Hidden = False
   
    ' asks for value
    TmpVal = InputBox("Enter the value you want to filter for", "Filter")

    ' filters each row that does not contain a match in column A.
    For Each TmpRng In Worksheets("Sheet1").UsedRange.Rows
        If TmpRng.Cells(1, 1).Value <> TmpVal Then
            TmpRng.EntireRow.Hidden = True
        End If
    Next
End Sub
********************************************************

WATYF

What does your list contain ? Sometimes you can 'split a column' like a name into family name and christian name, a part number into component and part, etc. But I doubt you will have this luck with all 21 columns...

I guess you will have to either use the Advanced Filter or move to A(bs)cess.

WATYF --
Am trying your suggestion, but keep getting hung up on the line:
If TmpRng.Cells(1, 1).Value <> TmpVal Then

What do (1,1) represent?
thanks

WATYF --
Am trying your suggestion, but keep getting hung up on the line:
If TmpRng.Cells(1, 1).Value <> TmpVal Then

What do (1,1) represent?
thanks

By "hung up", do you mean that an error occurs, or that you just want to understand what that means?

The Cells(1, 1).Value is basically returning the value for Row 1, Column 1 in the range. So, if you wanted to filter on Column B, it would have to say Cells(1 ,2).Value. (and so on)

BTW,.. I forgot to account for the header row (I'm assuming Row 1 contains a header)... so you'd want to use this instead:


********************************************************
Sub Test()

   Dim TmpVal As Integer
   Dim TmpRng As Range
   
   ' removes previous "filter".
   Cells.EntireRow.Hidden = False
   
   ' asks for value
   TmpVal = InputBox("Enter the value you want to filter for", "Filter")

   ' filters each row that does not contain a match in column A.
   For Each TmpRng In Worksheets("Sheet1").UsedRange.Rows
       If TmpRng.Cells(1, 1).Value <> TmpVal And TmpRng.Row <> 1 Then
           TmpRng.EntireRow.Hidden = True
       End If
   Next
End Sub

*******************************************************


As I mentioned, you could customize this however you wanted... (i.e. ask which column to apply the filter to, etc.)


WATYF

Or to make it simpler, you can use "Range" instead of "Cells"... that way you don't have to count the number of columns to figure out which number to use. (i.e. Column J = 10.) I also added an example of how to customize it for column selection.


********************************************************

Sub Test()

   Dim TmpVal As Integer
   Dim TmpRng As Range
   
   ' removes previous "filter".
   Cells.EntireRow.Hidden = False
   
   ' asks for value
   TmpVal = InputBox("Enter the value you want to filter for", "Filter Criteria")
   ' asks for column to filter
   TmpCol = InputBox("Enter the column to filter on", "Column Selection")

   ' hides each row that does not contain a match in the specified column
   For Each TmpRng In Worksheets("Sheet1").UsedRange.Rows
       If TmpRng.Range(TmpCol & "1").Value <> TmpVal And TmpRng.Row <> 1 Then
           TmpRng.EntireRow.Hidden = True
       End If
   Next
End Sub

*********************************************************


WATYF

SSparks, even if you decided to pursue WATYF's solution giving feedback would be helpful. Would you like to be dropped out of a discussion, especially if you try to help somebody ?  

Additionally: If you use the SUBTOTAL function to count/add etc. the filtered data, you will have to write your own function, as SUBTOTAL will include _hidden_ rows.

cri--

Sorry, not sure what your first comments mean.  This is the first time I have used this forum, so please forgive any faux pas. Due to my workload, it takes me some time to evaluate your suggestions, but the discussion and suggestions have been extremely helpful.  

I am not using subtotal function in the current workbook, but appreciate your thoughts.  Also, each column contains different customer information (ie, customer name, customer number, order total, etc.) which would not lend itself easily to being split into multiple additional columns.

WAYTF-
I was receiving a mismatch error and wanted to make sure I was giving the correct row, column combination.  I verfied that the columns I was initially using were formated as integers, but even after getting the row/column correct, I received an Autofilter method of Range class failed error.

Thanks,
CSparks

Hhhmmm.. interesting. I'm trying to reproduce your error, but it's running fine for me. Keep in mind that I'm using very simple test data, so it could be a difference in the data we're using. Try maybe leaving the TmpVal as a variant or a text string.

*********************************************************

Sub Test()

  Dim TmpVal As String
  Dim TmpCol As String
  Dim TmpRng As Range
 
  ' removes previous "filter".
  Cells.EntireRow.Hidden = False
 
  ' asks for value
  TmpVal = InputBox("Enter the value you want to filter for", "Filter Criteria")
  ' asks for column to filter
  TmpCol = InputBox("Enter the column to filter on", "Column Selection")

  ' hides each row that does not contain a match in the specified column
  For Each TmpRng In Worksheets("Sheet1").UsedRange.Rows
      If TmpRng.Range(TmpCol & "1").Value <> TmpVal And TmpRng.Row <> 1 Then
          TmpRng.EntireRow.Hidden = True
      End If
  Next
End Sub

*********************************************************


If necessary, you can send me sample data and I can try to debug the error.


WATYF

I finally got the last one you sent to run, but it didn't return the correct values--very strange...

The last one seemed to work, but then I got a Application-defined object error on the line:
If TmpRng.Range(3, TmpCol).Value <> TmpVal And TmpRng.Row <> 3 Then

ideas?

OPPS--

the line was:
If TmpRng.Range(TmpCol, "1").Value <> TmpVal And TmpRng.Row <> 1 Then

sorry... missed your comment while I was posting.

The reason this line won't work:

If TmpRng.Range(TmpCol, "1").Value <> TmpVal And TmpRng.Row <> 1 Then

is because you should be using an "&" instead of a comma.



WATYF

WATYF--

That was it!  Thanks for your assistance and patience!

Also, thanks to CRI for the info, too!

No problem. Glad I could help.


WATYF