VBA Autofilter 2003

[verluc]

How can I set an autofilter on a column, so that the filter takes not the empty "<>""" cells ?

 

[AlphaFrog]

Record a macro where you Autofilter for Blanks or NonBlanks. Then look at the recorded macro to see the example code.

 

[JLGWhiz]

How can I set an autofilter on a column, so that the filter takes not the empty "<>""" cells ?

You almost have it, it is "<>" for the criteria.

 

[verluc]

Thanks JLGWiz, this is now my macro, but it does'nt work.
Any idea ?

Sub Filter()
Application.ScreenUpdating = False
  Sheets("Selectie").Cells.ClearContents
  With Sheets("XXXXXXX").UsedRange
          x = InputBox("Columnnumber")
         If Val(x) = 0 Then Exit Sub
         .Range("A4:R1000").Interior.ColorIndex = 0
         .AutoFilter x, "<>"
         .Copy Sheets("Bank").Cells(65536, 1).End(xlUp)
         .AutoFilter
         .Columns(1).Resize(, .Columns.Count).AutoFit
  End With
         Sheets("Bank").Columns(1).Resize(Sheets("Selectie").Columns.Count).AutoFit
Application.ScreenUpdating = True
End Sub

 

[verluc]

ADVERTISEMENT

Sorry JLGMiz, I was to fast with my answer. It really works fine
Million thanks

 

[verluc]

Still an additional question about this Autofilter:

This filter works for column 15,16 and 17 (Autofilter x)

When 15 is choossen then after filtering : 16 and 17 delete
When 16 is choossen then after filtering : 15 and 17 delete
When 17 is choossen then after filtering : 15 and 16 delete

so at the end in the page "Bank", i have 1 column in place of 3
Is this possible ?

 

[JLGWhiz]

ADVERTISEMENT

Still an additional question about this Autofilter:

This filter works for column 15,16 and 17 (Autofilter x)

When 15 is choossen then after filtering : 16 and 17 delete
When 16 is choossen then after filtering : 15 and 17 delete
When 17 is choossen then after filtering : 15 and 16 delete

so at the end in the page "Bank", i have 1 column in place of 3
Is this possible ?

This snippet would evealuate which column is being filtered and delete the other two. The remaining column will always appear as column O. If you use it outside the With statement, be sure to remove the periods in front of Columns and Range.

If x = 15 Then
    .Columns("P:Q").Delete
ElseIf x = 16 Then
    .Range("O1, Q1").EntireColumn.Delete
ElseIf x = 17 Then
    .Columns("O:P").Delete
End If

 

[verluc]

Hello JLGWhiz,

Where to place your code in my macro (see nr.4)
Many thanks in advance.

 

[JLGWhiz]

Hello JLGWhiz,

Where to place your code in my macro (see nr.4)
Many thanks in advance.

Sub Filter()
Application.ScreenUpdating = False
    Sheets("Selectie").Cells.ClearContents
    With Sheets("XXXXXXX").UsedRange
        x = InputBox("Columnnumber")
        If val(x) = 0 Then Exit Sub
        .Range("A4:R1000").Interior.ColorIndex = 0
        .AutoFilter x, "<>"
        .Copy Sheets("Bank").Cells(65536, 1).End(xlUp)
        .AutoFilter
        .Columns(1).Resize(, .Columns.Count).AutoFit
        If x = 15 Then
            .Columns("P:Q").Delete
        ElseIf x = 16 Then
            .Range("O1, Q1").EntireColumn.Delete
        ElseIf x = 17 Then
            .Columns("O:P").Delete
        End If
    End With
    Sheets("Bank").Columns(1).Resize(Sheets("Selectie").Columns.Count).AutoFit
Application.ScreenUpdating = True
End Sub

 

[verluc]

Million thanks JLGWhiz !!!