AutoFilter Criteria Using Array (Error) - Too Large String?

Question

Update: Through some additional testing I discovered: 1) 255 Characters does seem to be the breaking point (character limit). Setting the filter with an array with a character length of 245 worked fine -- I was able to save and reopen without any errors. I added another criteria to the array to make the length 262, saved the file, and then got the same error. 2) The sheet in the removed records message refers to the sheet index, not the sheet name, and it does indeed reference the sheet with the autofiltering. End Update

My Issue -- I've written code to set a dataset's AutoFilter based on selected items in several slicers. Sometimes when I open up the file I get the error (paraphrased): Excel found unreadable content in the workbook. Do you want to repair the file? Then a dialog pops up and says Removed Records: Sorting from /xl/worksheets/sheet2.xml part.

The code works as designed; the dataset reflects whatever is selected in the slicers (even many selections).

I set the array (a string array) as follows and then use the array to set the criteria:

If sCache.Name = "Slicer_Test" Then
    For Each sItem In ActiveWorkbook.SlicerCaches(sCache.Name).SlicerItems
        If sItem.Selected = True Then
            ReDim Preserve sArr(0 To sCount)
            sArr(sCount) = sItem.Name
            sCount = sCount + 1
        End If
    Next sItem
filterRng.AutoFilter Field:=9, Criteria1:=sArr, Operator:=xlFilterValues
ReDim sArr(0 To 0)
End If

I replicate the above code for each slicer.

Where I think the problem stems from is that the three largest slicers contain 27, 120, and 322 items, respectively. So as you can imagine, when all the items in the largest slicer are selected, the array's string length is over 5K characters long... like I mentioned above, the code works as designed. I found this thread, which mentions a character maximum?

I've tried removing the filters before saving/closing the workbook, but that doesn't always work, and this file will be used by many other people. So I'm wondering if 1) anyone has a suggestion for a way to workaround this error, or 2) if there might be a way to accomplish the filtering without using a terribly-long array...

Any thoughts on this will be much appreciated!

If you can figure out why removing the filters doesn't seem to work, reliably, then maybe we can debug that? If that's a potential workaround, you could clear the sortfields on the _Close event (write the strings to a hidden worksheet or a named range, etc), and re-apply them with the workbook _Open event (retrieving the string from wherever you stored it, and then convert to an array using Split function). — David Zemens
– David Zemens, Commented Sep 27, 2013 at 3:59
@DavidZemens Thank you for your thoughts! I did try to clear the filters using the Before_Close event, and I thought that had fixed the issue, but alas, the error still arises randomly. Since the error is so inconsistent it's hard for me to debug. The filters don't necessarily need to be re-applied on _Open, which makes things a little easier. — ARich
– ARich, Commented Sep 27, 2013 at 4:26
@DavidZemens I've done some more testing. It seems that whether I remove the filters or not, the error persists. I did notice that I only get the error if I save the workbook after applying the array to the filter. I tried removing the filter criteria and then saving (which didn't work) and I tried removing the filters altogether before saving, but that didn't work either. I think that perhaps I'm going to need to find a different way to go about filtering by the neccesary criteria... — ARich
– ARich, Commented Sep 27, 2013 at 5:26

ARich · Accepted Answer · 2014-07-14 21:44:17Z

3

A co-worker of mine helped me resolve the issue.

Apparently when using this syntax:

Criteria1:=sArr

Excel reads the array as one long string instead of looking at it as an array that contains many string elements.

The fix is to use the Array() function like so:

Criteria1:=Array(sArr)

This seems to prevent Excel from corrupting.

answered Jul 14, 2014 at 21:44

ARich

3,2995 gold badges33 silver badges56 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Nickolay Over a year ago

FWIW, "read[ing] the array as one long string" was not a problem for me, but the length of individual entries does seem to be limited to 255 chars (including "=" at the beginning) - stackoverflow.com/q/49633705/1026

PASUMPON V N · Accepted Answer · 2014-07-03 08:01:40Z

0

Sorting before autofilter will help you to perform autofilter function faster and better

answered Jul 3, 2014 at 8:01

PASUMPON V N

1,1862 gold badges10 silver badges17 bronze badges

Collectives™ on Stack Overflow

AutoFilter Criteria Using Array (Error) - Too Large String?

2 Answers 2

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related