Can't make autofilter find the number "1" and not "10"

Hi experts,

I have a column with data, which is controlled by autofilter in VBA. As an exampel, some of the rows contains thees data:

Column H:
10
1,3,7,10
'empty
3,7,10

(all in same column - H!)

If I use the following code, all four lines returns (month=1)

Please Login or Register  to view this content.

The problem is, that "1" is also in the number "10"

I hope you understand my qustion, 'cause it's a little difficult to explain

Welcome to the Forum!

To look at the big picture, you are probably inviting trouble by put multiple numeric values into a single cell. This causes them to be managed as a string. And as you have found, a 1 contained in a string could be part of 1, 10, 11, 12, etc.

One modification that will cut down on false positives is to add a comma after the search. But you will then miss a 1 if it is the last number in a sequence, or the only number in the cell.

Please Login or Register  to view this content.

Excel's built-in pattern matching is a little limited. A more complex method would be to write your own filtering Sub that looks at every line and uses a more complex pattern matching approach, and then hide any row that doesn't match. I would do that with regexp (see implementation of utilities at http://www.tmehta.com/regexp/add_code.htm and a reference for regexp at http://www.regular-expressions.info/). You would then hide any row that does not match the pattern

month & "[^,$]"

(I have not tested this.)

However, I think the best way to do this is to redesign how you are recording your data. I can't give any advice on that without knowing more about what you are doing. Can you attach a sample workbook?