VBA Filter for $-
Results 1 to 8 of 8

Thread: VBA Filter for $-
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    Mar 2019
    Posts
    88
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default VBA Filter for $-

    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.

  2. #2
    Board Regular alansidman's Avatar
    Join Date
    Feb 2007
    Location
    Steamboat Springs
    Posts
    4,862
    Post Thanks / Like
    Mentioned
    7 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA Filter for $-

    Have you tried using the Macro recorder to see what the difference would be when you use that code.
    Let me know if that works for you
    Alan

    Am Yisrael Chai

    Win 10--Office 2019
    When Posting Code, please use code tags.
    How to insert Mcode to Power Query https://excel.solutions/2017/11/powe...te-code-video/


  3. #3
    Board Regular
    Join Date
    Mar 2019
    Posts
    88
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA Filter for $-

    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.

  4. #4
    Board Regular pike's Avatar
    Join Date
    Sep 2007
    Location
    Alstonville, Australia
    Posts
    227
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA Filter for $-

    Hi,
    Are you using the accounting cell format?
    $ -
    is a zero value
    If the solution helped please donate to RSPCA

    Sites worth visiting: Rabbitohs | Excel-it royUK | Excel Matters Rory | Kris' Spreadsheet Solutions | Domenic xl-central

  5. #5
    Board Regular
    Join Date
    Jun 2014
    Posts
    826
    Post Thanks / Like
    Mentioned
    5 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA Filter for $-

    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.

  6. #6
    Board Regular
    Join Date
    Mar 2019
    Posts
    88
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA Filter for $-

    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 by tanyaleblanc; Apr 22nd, 2019 at 08:03 AM.

  7. #7
    Board Regular
    Join Date
    Mar 2019
    Posts
    88
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA Filter for $-

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

  8. #8
    Board Regular
    Join Date
    Jun 2014
    Posts
    826
    Post Thanks / Like
    Mentioned
    5 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA Filter for $-

    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("A2:M" & lRow)
    and
    Code:
    With ActiveSheet.Range("A1", ActiveSheet.Range("M" & ActiveSheet.Rows.Count).End(xlUp))
    to
    Code:
    With ActiveSheet.Range("A2", ActiveSheet.Range("M" & ActiveSheet.Rows.Count).End(xlUp))
    However, based on your post#6, you now have the code working.

    Cheerio,
    vcoolio.

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •