VBA Filter for $-

[tanyaleblanc]

I'm trying to filter, not sure why its not workig, I seem to always have an issue with the does not <>, there's a formula in that cell, but it shouldn't matter I wouldn't think.

With ActiveSheet        .AutoFilterMode = False
        With Range("a2:m2" & lRow)
            .AutoFilter
            .AutoFilter Field:=2, Criteria1:="<>$- ", Operator:=xlAnd, Criteria2:="<>#N/A"
            MsgBox "stop"

'filter not working should pickup everything that's not $-

it's picking up, do not include N/A's but it's including the $- for some reason.

 

[alansidman]

Have you tried using the Macro recorder to see what the difference would be when you use that code.

 

[tanyaleblanc]

Its just a filter, if I use the macro recorder, I can only filter the column and choose the the data I want to see, I don't know how else to do it.

 

[pike]

Hi,
Are you using the accounting cell format?
$ -
is a zero value

 

[vcoolio]

Hello Tanya,

Change this line:-

With Range("a2:m2" & lRow)

to

With Range("a1:m" & lRow)

I'm assuming that you have headings in Row1 with data starting in Row2

or try it this way:-

With ActiveSheet.Range("A1", ActiveSheet.Range("M" & ActiveSheet.Rows.Count).End(xlUp))
        .AutoFilter 2, "<>" & "#N/A", xlAnd, "<>" & "$-"
End With

If this too doesn't work then a reply to Pike's question may unlock the mystery for you.

Cheerio,
vcoolio.

 

[tanyaleblanc]

Yes, it is the accounting cell format, I changed it to currency and updated the code to <>$0.00 and it works, tks again

 

[tanyaleblanc]

my headings are in row 2 and data starting in row 3, but the code above is still not working.

 

[vcoolio]

Hello Tanya,

my headings are in row 2 and data starting in row 3, but the code above is still not working.

In that case, change:-

With Range("A1:M" & lRow)

to

With Range("[COLOR=#ff0000]A2[/COLOR]:M" & lRow)

and

With ActiveSheet.Range("A1", ActiveSheet.Range("M" & ActiveSheet.Rows.Count).End(xlUp))

to

With ActiveSheet.Range("[COLOR=#ff0000]A2[/COLOR]", ActiveSheet.Range("M" & ActiveSheet.Rows.Count).End(xlUp))

However, based on your post#6, you now have the code working.

Cheerio,
vcoolio.