?
Solved

Excel autofilter limitation

Posted on 2003-03-25
23
Medium Priority
?
4,411 Views
Last Modified: 2007-12-19
Is there a row limit on the autofilter in Excel?  I have a file of 8,000 rows that will not autofilter past row 1017 (regardless of the column content or formatting).  If this is a limitation of Excel, are there any workarounds?  (My coworkers are not Acces-savy, so we use Excel as a database.)
0
Comment
Question by:CSparks
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 9
  • 7
  • 6
  • +1
23 Comments
 
LVL 16

Expert Comment

by:sebastienm
ID: 8205292
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
0
 
LVL 13

Expert Comment

by:cri
ID: 8206389
AFAIK you can not filter more than 1000 different items/descriptors per column.
0
 
LVL 13

Expert Comment

by:cri
ID: 8206399
0
Percona Live Europe 2017 | Sep 25 - 27, 2017

The Percona Live Open Source Database Conference Europe 2017 is the premier event for the diverse and active European open source database community, as well as businesses that develop and use open source database software.

 
LVL 11

Expert Comment

by:WATYF
ID: 8206493
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
0
 
LVL 13

Expert Comment

by:cri
ID: 8208261
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.
0
 
LVL 11

Expert Comment

by:WATYF
ID: 8211243
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
0
 

Author Comment

by:CSparks
ID: 8211305
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?
0
 
LVL 11

Expert Comment

by:WATYF
ID: 8212185
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
0
 
LVL 13

Expert Comment

by:cri
ID: 8212767
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.
0
 

Author Comment

by:CSparks
ID: 8213797
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
0
 

Author Comment

by:CSparks
ID: 8213879
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
0
 
LVL 11

Expert Comment

by:WATYF
ID: 8214079
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
0
 
LVL 11

Expert Comment

by:WATYF
ID: 8214121
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
0
 
LVL 13

Expert Comment

by:cri
ID: 8215726
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 ?  
0
 
LVL 13

Expert Comment

by:cri
ID: 8215735
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.
0
 

Author Comment

by:CSparks
ID: 8218061
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
0
 
LVL 11

Expert Comment

by:WATYF
ID: 8218603
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
0
 

Author Comment

by:CSparks
ID: 8218706
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?
0
 

Author Comment

by:CSparks
ID: 8218843
OPPS--

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

Accepted Solution

by:
WATYF earned 300 total points
ID: 8218893
Yes,.. you're trying to use a text string (the letter of the column) as an integer in the "Range" method. You also are using the "Range" method incorrectly. When referencing a cell with the Range method, it's Column first, then Row. (i.e. Range("A1")) when referencing a cell with the "Cells" method, it's Row first, then Column (i.e. Cells(1, 3))

You don't want to change the Row reference at all... it should always be "1".

So the line should read like this.

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


Also, you can't ask for the column if you use the Cells method, because when you enter the Column in the input box, you are entering a letter (i.e. "B"), but the Cells method reads columns as numbers... So, using an input box to enter the column when using the Cells method would require converting the letter to it's appropriate number (i.e. convert J to 10)


If you didn't return the correct values, that could be because the data type didn't match. (i.e. text to integer), or it could be because you tried to reference Row "3".


If you use this code, unaltered, you should be able to just enter the column you want to filter on and it will adjust.


*********************************************************
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 no filtering occurs when you run this code, then it must be because the data types don't match, so just change: Dim TmpVal As String to Dim TmpVal As Integer



WATYF
0
 
LVL 11

Expert Comment

by:WATYF
ID: 8218901
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
0
 

Author Comment

by:CSparks
ID: 8220192
WATYF--

That was it!  Thanks for your assistance and patience!

Also, thanks to CRI for the info, too!
0
 
LVL 11

Expert Comment

by:WATYF
ID: 8220512
No problem. Glad I could help.


WATYF
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Invest in your employees with these five simple steps to improve employee engagement and retention.
If you're a modern-day technology professional, you may be wondering if certifications are really necessary. They are. Here's why.
XMind Plus helps organize all details/aspects of any project from large to small in an orderly and concise manner. If you are working on a complex project, use this micro tutorial to show you how to make a basic flow chart. The software is free when…
This is used to tweak the memory usage for your computer, it is used for servers more so than workstations but just be careful editing registry settings as it may cause irreversible results. I hold no responsibility for anything you do to the regist…

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question