Results 1 to 6 of 6

Thread: VBA to apply advance filter to find blank cells
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    Jun 2008
    Posts
    1,234
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default VBA to apply advance filter to find blank cells

    this is my current code.

    Code:
    NR.Range("A1").Value = CS.Range("D5").Value   '(REQUIRED)
    NR.Range("A2").Value = "<" & Format(CDate(Now()), "mm/dd/yyyy")
    NR.Range("A3").Value = "<" & Format(CDate(Now()), "mm/dd/yyyy")
    
    
    NR.Range("B1").Value = CS.Range("O5").Value   '(DUE)
    NR.Range("B2").Value = "<" & Format(CDate(Now()), "mm/dd/yyyy")
    NR.Range("B3").Value = BLANK
    
    
    
    
    'APPLY FILTER
    CS.Range("A5:Z10000").AdvancedFilter _
        Action:=xlFilterCopy, _
        CriteriaRange:=NR.Range("A1:B2"), _
        CopyToRange:=NR.Range("A12"), _
        Unique:=False
    The Due column normally contains a date, i succesfully filter this date when it is before today.
    However I cannot find the correct syntax to filter this column when blank.


    in english...


    ('Required' = Before today AND 'Due' = Before today) OR
    ('Required' = Before today AND 'Due' = BLANK)

    any help is appreciated

  2. #2
    Board Regular Akuini's Avatar
    Join Date
    Feb 2016
    Location
    Indonesia
    Posts
    2,139
    Post Thanks / Like
    Mentioned
    43 Post(s)
    Tagged
    4 Thread(s)

    Default Re: VBA to apply advance filter to find blank cells

    Have you tried using equal sign "=" (without quotes in the cell but off course with quotes in vba)

    Code:
    NR.Range("B3").Value = "="

  3. #3
    Board Regular
    Join Date
    Jun 2008
    Posts
    1,234
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA to apply advance filter to find blank cells

    Thanks, But it didn't work.

    Maybe because column O contains a formula... (sorry for not mentioning it before )


    =IFERROR(IF(VLOOKUP(A6&"/"&B6,Comments!A:C,3,0)<10,"",VLOOKUP(A6&"/"&B6,Comments!A:C,3,0)),"")

    so I am looking for the ""

  4. #4
    Board Regular Akuini's Avatar
    Join Date
    Feb 2016
    Location
    Indonesia
    Posts
    2,139
    Post Thanks / Like
    Mentioned
    43 Post(s)
    Tagged
    4 Thread(s)

    Default Re: VBA to apply advance filter to find blank cells

    In that case try ="" in the cell or in vba:
    Code:
    NR.Range("B3").Value =  "="""""

  5. #5
    Board Regular
    Join Date
    Jun 2008
    Posts
    1,234
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA to apply advance filter to find blank cells

    That's got it thanks!!

  6. #6
    Board Regular Akuini's Avatar
    Join Date
    Feb 2016
    Location
    Indonesia
    Posts
    2,139
    Post Thanks / Like
    Mentioned
    43 Post(s)
    Tagged
    4 Thread(s)

    Default Re: VBA to apply advance filter to find blank cells

    You're welcome.

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
  •