Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 4428
  • Last Modified:

Excel autofilter limitation

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
CSparks
Asked:
CSparks
  • 9
  • 7
  • 6
  • +1
1 Solution
 
sebastienmCommented:
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
 
criCommented:
AFAIK you can not filter more than 1000 different items/descriptors per column.
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
WATYFCommented:
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
 
criCommented:
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
 
WATYFCommented:
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
 
CSparksAuthor Commented:
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
 
WATYFCommented:
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
 
criCommented:
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
 
CSparksAuthor Commented:
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
 
CSparksAuthor Commented:
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
 
WATYFCommented:
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
 
WATYFCommented:
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
 
criCommented:
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
 
criCommented:
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
 
CSparksAuthor Commented:
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
 
WATYFCommented:
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
 
CSparksAuthor Commented:
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
 
CSparksAuthor Commented:
OPPS--

the line was:
If TmpRng.Range(TmpCol, "1").Value <> TmpVal And TmpRng.Row <> 1 Then
0
 
WATYFCommented:
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
 
WATYFCommented:
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
 
CSparksAuthor Commented:
WATYF--

That was it!  Thanks for your assistance and patience!

Also, thanks to CRI for the info, too!
0
 
WATYFCommented:
No problem. Glad I could help.


WATYF
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

  • 9
  • 7
  • 6
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now