Trouble with Advanced Filter

[weinbrecht1]

Hello-

I have a snippet of VBA code to perform an advanced filter that has
been working like a charm for several years on a defined set of data.

I have just started to use this code on a new set of data, and for some
reason the value "I.3.1" will not filter. I am able to filter it using
autofilter, I have confirmed that the values in the criteria range are
exact, etc. Also, similar values I.3.2 work fine, I.4.1 work fine,
but I.3.1 will not filter.

The code
Worksheets("Sheet1").Select
Let lastrow = Application.CountA(ActiveSheet.Range("j:j")) + 3
Range("H5").Select
Selection.CurrentRegion.Select
Range("A5:H" & lastrow).AdvancedFilter Action:=xlFilterInPlace,
CriteriaRange:= _
Range("other")

Does anyone have any ideas about why the code works for all other
values except I.3.1?

Thanks for your help in advance,

Rachael

 

[Jim Rech]

There is nothing special about "I.3.1" (I just tried it to be sure) so my
guess is that the data does not match the criterion in this case. Just to
positively test this I'd suggest that you enter "I.3.1" in the criterion
cell and then enter a temporary formula like =A10=F2 somewhere where A10 is
a cell in the data list that has the apparent value of I.3.1 and F2 is the
criterion cell. If it's False you know the data has junk like an extra
space, etc., in it.

--
Jim
| Hello-
|
| I have a snippet of VBA code to perform an advanced filter that has
| been working like a charm for several years on a defined set of data.
|
| I have just started to use this code on a new set of data, and for some
| reason the value "I.3.1" will not filter. I am able to filter it using
| autofilter, I have confirmed that the values in the criteria range are
| exact, etc. Also, similar values I.3.2 work fine, I.4.1 work fine,
| but I.3.1 will not filter.
|
| The code
| Worksheets("Sheet1").Select
| Let lastrow = Application.CountA(ActiveSheet.Range("j:j")) + 3
| Range("H5").Select
| Selection.CurrentRegion.Select
| Range("A5:H" & lastrow).AdvancedFilter Action:=xlFilterInPlace,
| CriteriaRange:= _
| Range("other")
|
| Does anyone have any ideas about why the code works for all other
| values except I.3.1?
|
| Thanks for your help in advance,
|
| Rachael
|