Excel VBA filtering data without deleting blanks

Question

I have a question regarding filtering text in columns in Excel / VBA. Here is my starting point (can't post images):

Column1	Column2	Column3
value1	bla	bla
empty	bla	bla
value2	bla	bla
value1	bla	bla
empty	bla	bla
value2	bla	bla

What I want to do is to use a text filter on the "Column1" for "value1" so that the result includes the lines with the "empty" value in column1. The filtering result should look like that:

Column1	Column2	Column3
value1	bla	bla
empty	bla	bla
value1	bla	bla
empty	bla	bla

However, when I use a normal text filter in Excel on column1 for "value1" I get this result, without the lines that have "empty" in Column1:

Column1	Column2	Column3
value1	bla	bla
value1	bla	bla

What I lack is a general idea of how this could be done in Excel / VBA. Somehow the lines with empty should be "connected" to those with "value1" in kind of of a range? Any suggestions very much appreciated!!

@BigBen yes, thank you. However, this gives me all blanks, but I want only the blanks under the "good" values (in example above value1) and not those under the value2. So it should be kind of conditional... — matthias.krs
– matthias.krs, Commented Jul 14, 2021 at 21:32

Gary's Student · Accepted Answer · 2021-07-14 16:25:38Z

1

Use the Macro Recorder and then some cleanup:

Sub Macro1()
    With Range("A1:C7")
        .AutoFilter
        .AutoFilter Field:=1, Criteria1:="=value1", _
            Operator:=xlOr, Criteria2:="="
    End With
End Sub

answered Jul 14, 2021 at 16:25

Gary's Student

96.9k10 gold badges66 silver badges104 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

matthias.krs Over a year ago

Thank you for the response - I think I am half way there. As far as I understand it will this bring me all the blanks in the table. However, I would like to get only those blanks that stand under a "good value" (value1) and NOT those under value2. So it should be kind of conditional... Is that possible?

FunThomas · Accepted Answer · 2021-07-15 07:58:02Z

0

You can set the same Filter with VBA that you can set using the Filter in Excel. Using the Macro-Recorder reveals the syntax.

Assuming that you want to filter data in Columns A to C, you can use

ActiveSheet.Range("$A:$C").AutoFilter Field:=1, Criteria1:="=value1", _ 
    Operator:=xlOr, Criteria2:="="

As you can see, the search for blank is done using the filter criteria "="

If you want to see all values for more than one value plus blanks, use

ActiveSheet.Range("$A:$C").AutoFilter Field:=1, _
      Criteria1:=Array("value1", "value2", "="), Operator:=xlFilterValues

you put all values that you want to see into an Array, and that Array is passed as parameter to the Criteria1. Again, blank is represented by "=".

Update If you want to use Autofilter to show all rows that either have a specific value or blank and the previous row has this value, you can solve this with a helper column. Assuming your criteria is in column A, put a formula in the helper column: =IF(A2="",A1,A2) and filter on this column.

If you don't want a helper column, you will need to write a VBA routine that checks row by row if to show or hide this row.

edited Jul 15, 2021 at 7:58

answered Jul 14, 2021 at 16:24

FunThomas

31.3k4 gold badges24 silver badges40 bronze badges

1 Comment

matthias.krs Over a year ago

Thank you for the response - I think I am half way there. As far as I understand it, this will bring me all the blanks in the table. However, I would like to get only those blanks that stand under a "good value" (value1) and NOT those under value2. So it should be kind of conditional... Is that possible?

Collectives™ on Stack Overflow

Excel VBA filtering data without deleting blanks

2 Answers 2

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related