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
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.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 ?
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
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