VBA - Filter Out Blanks from Selected Column Macro

[kirkjd]

Hi All,

For years I have been struggling to come up with a macro to filter out blanks in a set of filtered data (rather than clicking on the filter button and scrolling down to (blanks) and unchecking the box).

Any ideas?

Thanks!

 

[NewOrderFac33]

How about a variation on the the last line of code in the example below?

ActiveSheet.Range("Database").AutoFilter Field:=1, Criteria1:="=NULL", Operator:=xlAnd      'EQUALS "NULL"
    'ActiveSheet.Range("Database").AutoFilter Field:=1, Criteria1:="<>NULL", Operator:=xlAnd     'DOES NOT EQUAL "NULL"
    'ActiveSheet.Range("Database").AutoFilter Field:=1, Criteria1:="=NULL*", Operator:=xlAnd     'BEGINS WITH "NULL"
    'ActiveSheet.Range("Database").AutoFilter Field:=1, Criteria1:="<>NULL*", Operator:=xlAnd    'DOES NOT BEGIN WITH "NULL"
    'ActiveSheet.Range("Database").AutoFilter Field:=1, Criteria1:="=*NULL", Operator:=xlAnd     'ENDS WITH "NULL"
    'ActiveSheet.Range("Database").AutoFilter Field:=1, Criteria1:="<>*NULL", Operator:=xlAnd    'DOES NOT END WITH "NULL"
    'ActiveSheet.Range("Database").AutoFilter Field:=1, Criteria1:="=*NULL*", Operator:=xlAnd    'CONTAINS "NULL"
    'ActiveSheet.Range("Database").AutoFilter Field:=1, Criteria1:="<>*NULL*", Operator:=xlAnd   'DOES NOT CONTAIN "NULL"
    'ActiveSheet.Range("Database").AutoFilter Field:=1, Criteria1:="="                          'Blanks
    'ActiveSheet.Range("Database").AutoFilter Field:=1, Criteria1:="<>"                         'NonBlanks

 

[wigi]

For years I have been struggling

That's quite long for something that is almost a oneliner:

Sub wigi()
    ActiveCell.CurrentRegion.AutoFilter ActiveCell.CurrentRegion.Column - ActiveCell.Column + 1, "<>"
End Sub

 

[NewOrderFac33]

What, my answer's quite long? I was only suggesting that the OP used the last line of code. I merely posted the rest as I thought he/she might find them useful also.

 

[wigi]

What, my answer's quite long? I was only suggesting that the OP used the last line of code. I merely posted the rest as I thought he/she might find them useful also.

Euhm... did you notice who and what text I quoted?

 

[NewOrderFac33]

Sorry. Friday afternoon and my brains are fried.

I had to make the following amendment to your suggestion to make it work in the database from where I extracted my code example.

ActiveCell.CurrentRegion.AutoFilter Field:=ActiveCell.CurrentRegion.Columns.Count - ActiveCell.Column + 1, Criteria1:="<>"

Probably something to do with the way my data's laid out, but this is a really good solution that I'll be able to use a lot.

Thank you.

pete

 

[kirkjd]

I am having issues with both variations -

Wigi, your code works perfectly for filtering out blanks from the first column in my filtered data set. Is it possible to make it filter out blanks from whichever column in the set I currently have selected, however?

Pete, I seem to receive an error when I try use your code variation.

Thanks!

 

[wigi]

Wigi, your code works perfectly for filtering out blanks from the first column in my filtered data set. Is it possible to make it filter out blanks from whichever column in the set I currently have selected, however?

This should also work, I tested it before posting the code.
Can you tell us the data layout and the steps you did to make the macro available in Excel?

 

[NewOrderFac33]

The following works for me:

Sub Version3()
    ActiveCell.CurrentRegion.AutoFilter (ActiveCell.Column - ActiveCell.CurrentRegion.Column) + 1, "<>"
End Sub

I turned wigi's formula around - you need to take the column number of the selected cell, subtract the column number of the first column of your database, then add 1 to the result.

Example

If the database is in columns B:E and the cursor is in column D (column 4 of the worksheet but column 3 of your database)
The first column of the database is in column B (column 2)
4-2 = 2
Add 1 to that gives you 3

If the database is in columns C:F and the cursor is in column F (column 6 of the worksheet but column 4 of your database)
The first column of the database is in column C (column 3)
6-3 = 3
Add 1 to that gives you 4

Hope this helps

Pete

 

[kirkjd]

Version 3 works great - thanks for your help!