A colleague has developed a workbook with long paragraphs in each cell of a
column he wishes to autofilter. I know there is a limit of 1000 cells that
autofilter will read but there are only about 180 cells in the column. The
autofilter only returns 50-60 cells.

On the other hand the cells often contain over 200 characters, and some
contain 700-1000 characters, so I feel that there must be a limit that is
being overtaken in this workbook. If I delete most of a cell content it will
be added to the autofilter list, and if I lengthen it again it disappears,
but I cannot see why.

Please would someone suggest where precisely this is going awry?

The limits you are running into are documented here.

http://www.contextures.com/xlautofilter02.html#String

Mike

"Jonathan589" wrote:

A colleague has developed a workbook with long paragraphs in each cell of a
column he wishes to autofilter. I know there is a limit of 1000 cells that
autofilter will read but there are only about 180 cells in the column. The
autofilter only returns 50-60 cells.

On the other hand the cells often contain over 200 characters, and some
contain 700-1000 characters, so I feel that there must be a limit that is
being overtaken in this workbook. If I delete most of a cell content it will
be added to the autofilter list, and if I lengthen it again it disappears,
but I cannot see why.

Please would someone suggest where precisely this is going awry?

Mike, thank you for your fast response. I recognised the article, but had not
connected my problem with that part of it. I will experiment wth shortening
all cells to fewer than 255 characters each. I have recommended to my
colleague that he splits the cells anyway to contain fewer data.

Regards Jonathan

"Mike H" wrote:

The limits you are running into are documented here.

http://www.contextures.com/xlautofilter02.html#String

Mike

"Jonathan589" wrote:

A colleague has developed a workbook with long paragraphs in each cell of a
column he wishes to autofilter. I know there is a limit of 1000 cells that
autofilter will read but there are only about 180 cells in the column. The
autofilter only returns 50-60 cells.

On the other hand the cells often contain over 200 characters, and some
contain 700-1000 characters, so I feel that there must be a limit that is
being overtaken in this workbook. If I delete most of a cell content it will
be added to the autofilter list, and if I lengthen it again it disappears,
but I cannot see why.

Please would someone suggest where precisely this is going awry?