PDA

View Full Version : vba autofilter with several search cells as a filter



jvmalssen88
06-20-2018, 11:36 PM
Good morning all,

I would like some assistance regarding a certain particular challenge i am facing right now, it regards a excel sheet with a lot of rules containing data. And i have an Auto filter activated on the entire range. But now to push it to the next level i want to be able to type keywords in certain cells in the sheet and let the auto filter automatically filter on the keywords i type in these cell(s). These 2 cells are: B3 - E3, and B3 is the search cell for column 2 of the range E3 of column 4. I can get it to work for the first search cell, i have this code in the works right now:


Private Sub Worksheet_Change(ByVal Target As Range)

If Intersect(Target, Range("B3")) Is Nothing Then Exit Sub
FilterWaarde = Range("B3").Value

If FilterWaarde = "" Then AutoFilter.ShowAllData: Exit Sub

With Sheets("Transport tarieven Verkoop")
.Range("B6:AC250").AutoFilter Field:=2, Criteria1:=FilterWaarde



End With


End Sub





My knowledge is not sufficient as how to add more cells to let the autofilter work with smoothly i can only get it to work with 1 cell, please help me regarding this issue, because i see the light at the end of the tunnel but i cannot do it on my own.

greetz

Jeffrey

p45cal
06-21-2018, 06:54 AM
try:
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("B3")) Is Nothing Then
FilterWaarde = Range("B3").Value
If FilterWaarde = "" Then
Range("B6:AC250").AutoFilter Field:=2
' AutoFilter.ShowAllData
Else
Range("B6:AC250").AutoFilter Field:=2, Criteria1:=FilterWaarde
End If
End If

If Not Intersect(Target, Range("E3")) Is Nothing Then
FilterWaarde = Range("E3").Value
If FilterWaarde = "" Then
Range("B6:AC250").AutoFilter Field:=4
' AutoFilter.ShowAllData
Else
Range("B6:AC250").AutoFilter Field:=4, Criteria1:=FilterWaarde
End If
End If
End Sub

p45cal
06-21-2018, 07:17 AM
Grrrr.
You've cross-posted this question in at least 4 other places without telling us where.
Have a read of http://www.excelguru.ca/content.php?184
Nearly all forums have the same cross-posting rules.