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!

 

[kirkjd]

The following works for me:

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

Pete

Would it be possible to modify this code so that rather than filtering blanks out of the selected column, it filters the value you currently have selected out of the selected column?

 

[NewOrderFac33]

Replying from non-Excel tablet, so will give this a look at work in the morning!

 

[kirkjd]

Would it be possible to modify this code so that rather than filtering blanks out of the selected column, it filters the value you currently have selected out of the selected column?

I've actually sorted out a simple solution for this:

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

 

[NewOrderFac33]

You read my mind - here are the four permutations that you might need to use:

Sub DynamicFilter()
    
    'Filter the database on the field containing the ActiveCell to display rows where the value in the ActiveCell's column is BLANK
    ActiveCell.CurrentRegion.AutoFilter (ActiveCell.Column - ActiveCell.CurrentRegion.Column) + 1, Criteria1:="="
    
    'Filter the database on the field containing the ActiveCell to display rows where the value in the ActiveCell's column is NONBLANK
    ActiveCell.CurrentRegion.AutoFilter (ActiveCell.Column - ActiveCell.CurrentRegion.Column) + 1, Criteria1:="<>"
    
    'Filter the database on the field containing the ActiveCell to display rows where the value in the ActiveCell's column is EQUAL TO the value in the ActiveCell
    ActiveCell.CurrentRegion.AutoFilter (ActiveCell.Column - ActiveCell.CurrentRegion.Column) + 1, Criteria1:="=" & ActiveCell.Value
    
    'Filter the database on the field containing the ActiveCell to display rows where the value in the ActiveCell's column is NOT EQUAL TO the value in the ActiveCell
    ActiveCell.CurrentRegion.AutoFilter (ActiveCell.Column - ActiveCell.CurrentRegion.Column) + 1, Criteria1:="<>" & ActiveCell.Value


End Sub

Cheers

Pete

 

[NewOrderFac33]

...and here are all the other possible variations included in my earlier post:

Sub DynamicFilter()
    
    'Filter the database on the field containing the ActiveCell to display rows where the value in the ActiveCell's column is BLANK
    ActiveCell.CurrentRegion.AutoFilter ((ActiveCell.Column - ActiveCell.CurrentRegion.Column) + 1), Criteria1:="="

    'Filter the database on the field containing the ActiveCell to display rows where the value in the ActiveCell's column is NONBLANK
    ActiveCell.CurrentRegion.AutoFilter ((ActiveCell.Column - ActiveCell.CurrentRegion.Column) + 1), Criteria1:="<>"

    'Filter the database on the field containing the ActiveCell to display rows where the value in the ActiveCell's column IS EQUAL TO the value in the ActiveCell
    ActiveCell.CurrentRegion.AutoFilter ((ActiveCell.Column - ActiveCell.CurrentRegion.Column) + 1), Criteria1:="=" & ActiveCell.Value

    'Filter the database on the field containing the ActiveCell to display rows where the value in the ActiveCell's column IS NOT EQUAL TO the value in the ActiveCell
    ActiveCell.CurrentRegion.AutoFilter ((ActiveCell.Column - ActiveCell.CurrentRegion.Column) + 1), Criteria1:="<>" & ActiveCell.Value

    'Filter the database on the field containing the ActiveCell to display rows where the value in the ActiveCell's column IS EQUAL TO the value "Cat"
    ActiveCell.CurrentRegion.AutoFilter ((ActiveCell.Column - ActiveCell.CurrentRegion.Column) + 1), Criteria1:="=Cat"

    'Filter the database on the field containing the ActiveCell to display rows where the value in the ActiveCell's column IS NOT EQUAL TO the value "Cat"
    ActiveCell.CurrentRegion.AutoFilter ((ActiveCell.Column - ActiveCell.CurrentRegion.Column) + 1), Criteria1:="<>Cat"

    'Filter the database on the field containing the ActiveCell to display rows where the value in the ActiveCell's column BEGINS WITH the value "Cat"
    ActiveCell.CurrentRegion.AutoFilter ((ActiveCell.Column - ActiveCell.CurrentRegion.Column) + 1), Criteria1:="Cat*"

    'Filter the database on the field containing the ActiveCell to display rows where the value in the ActiveCell's column DOES NOT BEGIN WITH the value "Cat"
    ActiveCell.CurrentRegion.AutoFilter ((ActiveCell.Column - ActiveCell.CurrentRegion.Column) + 1), Criteria1:="<>Cat*"

    'Filter the database on the field containing the ActiveCell to display rows where the value in the ActiveCell's column ENDS WITH the value "Cat"
    ActiveCell.CurrentRegion.AutoFilter ((ActiveCell.Column - ActiveCell.CurrentRegion.Column) + 1), Criteria1:="=*Cat"

    'Filter the database on the field containing the ActiveCell to display rows where the value in the ActiveCell's column DOES NOT END WITH the value "Cat"
    ActiveCell.CurrentRegion.AutoFilter ((ActiveCell.Column - ActiveCell.CurrentRegion.Column) + 1), Criteria1:="<>*Cat"

    'Filter the database on the field containing the ActiveCell to display rows where the value in the ActiveCell's column CONTAINS the value "Cat"
    ActiveCell.CurrentRegion.AutoFilter ((ActiveCell.Column - ActiveCell.CurrentRegion.Column) + 1), Criteria1:="=*Cat*"

    'Filter the database on the field containing the ActiveCell to display rows where the value in the ActiveCell's column is DOES NOT CONTAIN the value "Cat"
    ActiveCell.CurrentRegion.AutoFilter ((ActiveCell.Column - ActiveCell.CurrentRegion.Column) + 1), Criteria1:="<>*Cat*"

End Sub