bellaexcel
New Member
- Joined
- Aug 14, 2018
- Messages
- 10
I am trying to figure a way out to make advanced filter work by displaying results which has blank in them.
Currently, my advanced filter will only show results which are complete (rows which do not have any blank cells in them). I understand that you can use ="=" in the cell to showcase blank cell but for criterias which I leave blank, I would like my advanced filter's results to showcase both the blank results as well as results with answers.
For example:
I have these as my raw data:
F Name L Name Order Qty BirthPlace
John Wood 1 Texas
Pete Silva 2 NY
Jim Fox BF
Marc Phillips 2 Las Vegas
Louis Tyler 5 BF
When I input my criteria for BirthPlace to be "BF", only Louis Tyler will show up as his is a complete set but Jim Fox has a blank and thus he will not show up.
Any idea on what i can tweak in my formula to make the blanks i.e Jim Fox show up?
My current code is a simple one:
Sub GenerateData()
'
' GenerateData Macro
'
Sheets("Sheet1").Range("A1:Q3530").AdvancedFilter Action:=xlFilterCopy, _
CriteriaRange:=Range("C11:Q12"), CopyToRange:=Range("B14:R14"), Unique:= _
False
ActiveWindow.ScrollColumn = 1
End Sub
Thank you!
Currently, my advanced filter will only show results which are complete (rows which do not have any blank cells in them). I understand that you can use ="=" in the cell to showcase blank cell but for criterias which I leave blank, I would like my advanced filter's results to showcase both the blank results as well as results with answers.
For example:
I have these as my raw data:
F Name L Name Order Qty BirthPlace
John Wood 1 Texas
Pete Silva 2 NY
Jim Fox BF
Marc Phillips 2 Las Vegas
Louis Tyler 5 BF
When I input my criteria for BirthPlace to be "BF", only Louis Tyler will show up as his is a complete set but Jim Fox has a blank and thus he will not show up.
Any idea on what i can tweak in my formula to make the blanks i.e Jim Fox show up?
My current code is a simple one:
Sub GenerateData()
'
' GenerateData Macro
'
Sheets("Sheet1").Range("A1:Q3530").AdvancedFilter Action:=xlFilterCopy, _
CriteriaRange:=Range("C11:Q12"), CopyToRange:=Range("B14:R14"), Unique:= _
False
ActiveWindow.ScrollColumn = 1
End Sub
Thank you!