AutoFilter VBA no Blanks

ItalianPlatinum

Well-known Member
Joined
Mar 23, 2017
Messages
733
Office Version
  1. 2021
  2. 2019
  3. 2016
  4. 2010
  5. 2007
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))
 

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".
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))
 
Upvote 0
Hello ,

Does this code solve your issue?

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

Best,
vds1
 
Upvote 0
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
 
Upvote 0
In that case your cells are blank & not 0 displaying -
I used the code you provided to get the results I posted.
 
Upvote 0
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
 
Upvote 0
Glad you sorted it & thanks for the feedback.
 
Upvote 0

Forum statistics

Threads
1,213,531
Messages
6,114,167
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top