VBA to apply advance filter to find blank cells

kgkev

Well-known Member
Joined
Jun 24, 2008
Messages
1,243
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 = [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

Well-known Member
Joined
Feb 1, 2016
Messages
2,380
Office Version
2013
Platform
Windows
Have you tried using equal sign "=" (without quotes in the cell but off course with quotes in vba)

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

kgkev

Well-known Member
Joined
Jun 24, 2008
Messages
1,243
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

Well-known Member
Joined
Feb 1, 2016
Messages
2,380
Office Version
2013
Platform
Windows
In that case try ="" in the cell or in vba:
Code:
NR.Range("B3").Value =  "="""""
 

Akuini

Well-known Member
Joined
Feb 1, 2016
Messages
2,380
Office Version
2013
Platform
Windows
You're welcome.:)
 

Forum statistics

Threads
1,082,585
Messages
5,366,466
Members
400,892
Latest member
lamarh755

Some videos you may like

This Week's Hot Topics

Top