VBA Filter for $-

tanyaleblanc

Board Regular
Joined
Mar 16, 2019
Messages
139
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.

Code:
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.
 

Some videos you may like

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.

alansidman

Well-known Member
Joined
Feb 26, 2007
Messages
5,683
Office Version
2019
Platform
Windows
Have you tried using the Macro recorder to see what the difference would be when you use that code.
 

tanyaleblanc

Board Regular
Joined
Mar 16, 2019
Messages
139
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

Board Regular
Joined
Sep 16, 2007
Messages
231
Hi,
Are you using the accounting cell format?
$ -
is a zero value
 

vcoolio

Well-known Member
Joined
Jun 29, 2014
Messages
988
Hello Tanya,

Change this line:-

Code:
With Range("a2:m2" & lRow)
to
Code:
With Range("a1:m" & lRow)
I'm assuming that you have headings in Row1 with data starting in Row2

or try it this way:-

Code:
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

Board Regular
Joined
Mar 16, 2019
Messages
139
Yes, it is the accounting cell format, I changed it to currency and updated the code to <>$0.00 and it works, tks again
 
Last edited:

tanyaleblanc

Board Regular
Joined
Mar 16, 2019
Messages
139
my headings are in row 2 and data starting in row 3, but the code above is still not working.
 

vcoolio

Well-known Member
Joined
Jun 29, 2014
Messages
988
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:-

Code:
With Range("A1:M" & lRow)
to
Code:
With Range("[COLOR=#ff0000]A2[/COLOR]:M" & lRow)
and
Code:
With ActiveSheet.Range("A1", ActiveSheet.Range("M" & ActiveSheet.Rows.Count).End(xlUp))
to
Code:
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,102,302
Messages
5,486,054
Members
407,529
Latest member
netojose

This Week's Hot Topics

Top