Dear All,
I am looking for Code which sets a filter depending on the value of cell D2 in different columns.
If D2 equals All no filter is set or a set filter is cleared.
IF D2 equals A the filter in column D is set to 1.
IF D2 equals B the filter in column E is set to 1.
...
IF D2 equals G the filter in column I is set to 1. If D2 contained a letter beforehand the filter of the respective column has to be cleared. Only the filter in column I can be set.
I have an idea how to set a filter in one column. But in this case I need the code to change the filtered column depending on the value. Also I have the problem that the code below is fixed to Table1. Since the worksheet will be copied I have the problem that the reference to the new table needs to modified automatically. Is it possible to automate it?
Another problem is that the code only accepts numbers for the different cases and no letters.
Finally the code produces errors whenever I add a new row or change the content of a cell. Why is this happening?
Private Sub Worksheet_Change(ByVal Target As Range)
Dim lSelection As Long, stFilter As String
If Target = Range("D2") Then
lSelection = Range("D2")
Select Case lSelection
Case Is = All [comment: accepts no letters]
stFilter = "*"
Case Is = A [comment: accepts no letters]
stFilter = "1"
Case Is = B [comment: accepts no letters]
stFilter = "1"
Case Else
'default code
End Select
ActiveSheet.ListObjects("Table1").Range.AutoFilter Field:=3, Criteria1:=stFilter
End If
End Sub
Please find the example attached.
Many thanks.
Best regards,
fxmu
Bookmarks