VBA to apply advance filter to find blank cells

[kgkev]

this is my current 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 = [COLOR=#ff0000]BLANK[/COLOR]




'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

 

[Akuini]

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

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

 

[kgkev]

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 ""

 

[Akuini]

In that case try ="" in the cell or in vba:

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

 

[kgkev]

That's got it thanks!!

 

[Akuini]

You're welcome.