CODEWITHSUNDEEP
excelvbaautofilter
Chri.s
Chri.s

Reputation: 1466

VBA Autofilter criteria: apply if ALL cells in current range/row is blank

How do I apply an autofilter criteria that only applies if EVERY cell in range, let's say a row of 10 cells, are blank.

I tried the approach below but the autofilter isn't applied this way.

With range
     .AutoFilter Field:=1, Criteria1:=Application.WorksheetFunction.CountA(Range("A5:J5")) = 0
End with

The aim is to hide rows within the range in which all the cells are blank. It's required that ALL the cells are blank, not just 9/10 or 1/10 for example.

I'm surprised I wasn't able to find a similar question anywhere and I'm wondering whether my search is simply wrong - but that doesn't change the fact that I can't find similar questions or answers. So in advance, sorry if this already has been asked.

Upvotes: 1

Views: 248

Answers (2)

DisplayName
DisplayName

Reputation: 13386

if you don't mind using a "helper" column you could avoid AutoFilter(): 

With myRange
    With .Resize(, .Columns.Count + 1) ' enlarge referenced range by one "helper" column and reference this latter
        .Offset(, .Columns.Count - 1).Resize(, 1).FormulaR1C1 = "=IF(COUNTA(RC1:RC[-1])=0,1, """")" ' fill referenced "helper" range with a formula outputting a number if condition is met
        .SpecialCells(xlCellTypeFormulas, xlNumbers).EntireRow.Hidden = True ' hide referenced range rows with a numeric value
        .Offset(, .Columns.Count - 1).Resize(, 1).ClearContents ' clear helper column formula
    End With
End With

in this example the "helper" column is just one column right of referenced range

but the code could be tweaked to use whatever "helper" column

Upvotes: 2

user4039065
user4039065

Reputation:

Just stack 10 fields of filtering. One does not overwrite the other, each augments the previous filter set.

To hide rows where columns 1-10 are blank,

With range
     .AutoFilter Field:=1, Criteria1:="<>"
     .AutoFilter Field:=2, Criteria1:="<>"
     .AutoFilter Field:=3, Criteria1:="<>"
     .AutoFilter Field:=4, Criteria1:="<>"
     .AutoFilter Field:=5, Criteria1:="<>"
     .AutoFilter Field:=6, Criteria1:="<>"
     .AutoFilter Field:=7, Criteria1:="<>"
     .AutoFilter Field:=8, Criteria1:="<>"
     .AutoFilter Field:=9, Criteria1:="<>"
     .AutoFilter Field:=10, Criteria1:="<>"
End with

To expose rows where columns 1-10 are blank for deletion or other operation,

With range
     .AutoFilter Field:=1, Criteria1:="="
     .AutoFilter Field:=2, Criteria1:="="
     .AutoFilter Field:=3, Criteria1:="="
     .AutoFilter Field:=4, Criteria1:="="
     .AutoFilter Field:=5, Criteria1:="="
     .AutoFilter Field:=6, Criteria1:="="
     .AutoFilter Field:=7, Criteria1:="="
     .AutoFilter Field:=8, Criteria1:="="
     .AutoFilter Field:=9, Criteria1:="="
     .AutoFilter Field:=10, Criteria1:="="
End with

Upvotes: 1

Related Questions