AutoFilter VBA no Blanks

ItalianPlatinum

Active Member
Joined
Mar 23, 2017
Messages
287
Office Version
  1. 2016
  2. 2010
Platform
  1. Windows
Hello - This may be me fundamentally not understanding something but I have tested this multiple times and I cant get to what I want. I really just want to unfilter any blank cells in row 21. Per the below should work, "i think."

VBA Code:
If Sheets("Compare").AutoFilterMode Then Sheets("Compare").AutoFilterMode = False
Sheets("Compare").Range("12:12").AutoFilter field:=21, Criteria1:="<>"

but the problem i see if i have 0.00 in accounting format so "-", the above filter just filters off everything. blanks and "-". what am i doing wrong? is it because i have this formula in that column?

=IF(OR(B13=0,B13="",C13=1),"",(G13-O13))
 

Some videos you may like

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
48,520
Office Version
  1. 365
Platform
  1. Windows
Your code only hides the rows that are "blank"

+Fluff New.xlsm
ABCDEFGHIJKLMNOPQRSTU
12abcdefghijklmnopqrstu
1321111-
1521111-
16215114.00
1721156.00
20220119.00
2221111-
2321111-
Main
Cell Formulas
RangeFormula
U13,U15:U17,U20,U22:U23U13=IF(OR(B13=0,B13="",C13=1),"",(G13-O13))
 

vds1

Well-known Member
Joined
Oct 5, 2011
Messages
1,194

ADVERTISEMENT

Hello ,

Does this code solve your issue?

VBA Code:
Sheets("Compare").Range("12:12").AutoFilter Field:=21, Criteria1:="<>", Criteria2:="<>0", Operator:=xlAnd

Best,
vds1
 

ItalianPlatinum

Active Member
Joined
Mar 23, 2017
Messages
287
Office Version
  1. 2016
  2. 2010
Platform
  1. Windows
Your code only hides the rows that are "blank"

+Fluff New.xlsm
ABCDEFGHIJKLMNOPQRSTU
12abcdefghijklmnopqrstu
1321111-
1521111-
16215114.00
1721156.00
20220119.00
2221111-
2321111-
Main
Cell Formulas
RangeFormula
U13,U15:U17,U20,U22:U23U13=IF(OR(B13=0,B13="",C13=1),"",(G13-O13))
Right and that is what I want it to do but it isn't doing that
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
48,520
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

In that case your cells are blank & not 0 displaying -
I used the code you provided to get the results I posted.
 

ItalianPlatinum

Active Member
Joined
Mar 23, 2017
Messages
287
Office Version
  1. 2016
  2. 2010
Platform
  1. Windows
In that case your cells are blank & not 0 displaying -
I used the code you provided to get the results I posted.
I wanted to get back to you. I found the issue i was running into i had the below after my filtering code. therefore once i moved it to be prior to my filtering code it yielded the above. Bonehead misstep.

Application.Calculation = xlCalculationAutomatic
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
48,520
Office Version
  1. 365
Platform
  1. Windows
Glad you sorted it & thanks for the feedback.
 

Watch MrExcel Video

Forum statistics

Threads
1,114,653
Messages
5,549,212
Members
410,905
Latest member
Extjel
Top